Joining of tables Sub Queries

Joining of tables 

Joining tables is a fundamental operation in relational databases that allows you to combine data from two or more tables based on a related column. This operation is crucial for querying and analyzing data stored in a database. There are several types of joins in SQL, the most common being INNER JOIN, LEFT JOIN (or LEFT OUTER JOIN), RIGHT JOIN (or RIGHT OUTER JOIN), and FULL JOIN (or FULL OUTER JOIN). 

1.INNER JOIN:

• Returns only the rows that have matching values in both tables.

• Rows that do not have a match in the other table are excluded from the result set.

2.LEFT JOIN (LEFT OUTER JOIN):

• Returns all rows from the left table and the matched rows from the right table.

• If there is no match in the right table, NULL values are included for the columns from the right table.

Syntax:-

3.RIGHT JOIN (RIGHT OUTER JOIN):

• Returns all rows from the right table and the matched rows from the left table.

• If there is no match in the left table, NULL values are included for the columns from the left table

Syntax:-

4.FULL JOIN (FULL OUTER JOIN):

• Returns all rows when there is a match in either the left or the right table.

• If there is no match in one of the tables, NULL values are included for the columns from the table without a match.

Syntax:-

When performing a join, it's essential to specify the columns on which you want to join the tables. These columns should have compatible data types or can be explicitly converted to compatible data types.

Here's a simple example using two hypothetical tables, "orders" and "customers," to demonstrate an INNER JOIN:

This query retrieves the order IDs and customer names for orders that have matching customer IDs in both the "orders" and "customers" tables. 

Sub Queries

A subquery in SQL is essentially a query nested within another query, commonly found within the WHERE clause of the main SQL query. Here are some fundamental rules and guidelines for using subqueries:

1. **Purpose of Subqueries**: Subqueries are employed to retrieve data that will be used by the main query for filtering, comparison, or as a part of calculations.

2. **Location**: Subqueries are typically placed within parentheses and inside the WHERE clause of the main query. They can also be used in other clauses like SELECT, FROM, or HAVING when necessary.

3. **Comparison Operators**: Subqueries often use comparison operators such as =, <, >, <=, >=, or IN to relate the results of the subquery with the main query.

4. **Single-Row or Multi-Row Results**: Subqueries can return either a single value or a set of values (single-row or multi-row results). The choice depends on the specific use case and the operator used.

5. **Subquery Types**: Subqueries can be categorized into various types, including scalar subqueries (returning a single value), single-row subqueries (returning one row), and multi-row subqueries (returning multiple rows).

6. **Alias and Correlation**: When using subqueries in the SELECT clause, it's often necessary to assign an alias to the subquery. Additionally, correlated subqueries refer to the main query's tables, allowing for more complex relationships between the subquery and the main query.

7. **Performance Considerations**: Be cautious when using subqueries, especially correlated subqueries, as they can impact query performance. In some cases, alternative methods like JOINs or CTEs (Common Table Expressions) might be more efficient.

8. **Subquery Limitations**: Some database systems have limitations on the complexity and nesting depth of subqueries. Always consult the documentation for your specific database system to understand its capabilities and limitations regarding subqueries.

Syntax: 

While there isn't a single, universal syntax for subqueries in SQL because their usage can vary depending on the specific query and database system, subqueries are commonly used in conjunction with the SELECT statement. Here's a general template for a subquery within a SELECT statement: 

In this template:

• column1, column2, etc., represent the columns you want to retrieve in your main query.

• table1 is the main table you're querying.

• columnN is a column from table1 that you want to compare using an operator.

• operator is a comparison operator like =, <, >, IN, etc.

• The subquery within parentheses (SELECT columnX FROM tableY WHERE condition) is embedded in the WHERE clause and retrieves data from another table (tableY) based on a specific condition.

Subqueries with the INSERT Statement 

Subqueries can also be used with the INSERT statement in SQL to insert data into a table based on the results of a subquery. This allows you to populate a table with data derived from another table or the result of a subquery. Here's a basic syntax for using subqueries with the INSERT statement: 

In this syntax:

• target_table is the table into which you want to insert data.

• column1, column2, etc., are the columns in target_table where you want to insert data.

• expression1, expression2, etc., are expressions or values that you want to insert into the corresponding columns in target_table.

• source_table is the table or subquery that provides the data you want to insert.

• condition is an optional condition that filters the data from the source_table before insertion.

Here's an example to illustrate how you might use a subquery with the INSERT statement:

Let's say you have two tables, employees and new_hires, and you want to insert data into the employees table from the new_hires table based on a certain condition:

In this example, the INSERT statement retrieves data from the new_hires table, specifically the employee_id, employee_name, and salary columns, but only for records where the new_salary is greater than 50,000. It then inserts this data into the employees table 

Subqueries with the SELECT Statement 

Subqueries can be used with the SELECT statement in SQL to retrieve data from one or more tables based on the results of a nested query. This allows you to create more complex and dynamic queries by incorporating the results of one query into another. Here's the basic syntax for using subqueries with the SELECT statement: 

In this syntax:

• column1, column2, etc., represent the columns you want to retrieve in your main query.

• table1 is the main table you're querying.

• columnN is a column from table1 that you want to compare using an operator.

• operator is a comparison operator like '=', '<',' >', 'IN', etc.

• The subquery within parentheses (SELECT columnX FROM tableY WHERE condition) is embedded in the WHERE clause and retrieves data from another table (tableY) based on a specific condition.

Here's an example to illustrate how you might use a subquery with the SELECT statement:

Suppose you have two tables, employees and salaries, and you want to retrieve the names of employees who earn a salary greater than the average salary in the salaries table:

In this example, the main SELECT statement retrieves the employee_name from the employees table for employees whose employee_id matches the result of the subquery. The subquery calculates the average salary from the salaries table and then filters employees who earn more than the calculated average. 

Subqueries with the UPDATE Statement 

Subqueries can also be used with the UPDATE statement in SQL to modify data in a table based on the results of a subquery. This allows you to update records in one table using information from another table or using the results of a subquery. Here's a basic syntax for using subqueries with the UPDATE statement: 

In this syntax:

• target_table is the table you want to update.

• column1, column2, etc., are the columns in target_table that you want to update.

• value1, value2, etc., are the new values you want to set for the corresponding columns.

• columnN is a column in target_table that you want to compare using an operator.

• operator is a comparison operator like =, <, >, IN, etc.

• The subquery within parentheses (SELECT columnX FROM source_table WHERE condition) is embedded in the WHERE clause and retrieves data from another table (source_table) based on a specific condition.

Here's an example to illustrate how you might use a subquery with the UPDATE statement:

Suppose you have two tables, orders and customers, and you want to update the customer_id in the orders table based on a customer's name from the customers table 

In this example, the UPDATE statement updates the customer_id column in the orders table with the result of the subquery. The subquery retrieves the customer_id from the customers table for the customer with the name 'John Doe,' and this value is used to update the specified order (order_id = 123) in the orders table. 

Subqueries with the DELETE Statement 

Subqueries can also be used with the DELETE statement in SQL to remove rows from a table based on the results of a subquery. This allows you to delete specific records in a table using information derived from another table or a subquery's results. Here's a basic syntax for using subqueries with the DELETE statement: 

In this syntax:

• target_table is the table from which you want to delete rows.

• columnN is a column in target_table that you want to compare using an operator.

• operator is a comparison operator like =, <, >, IN, etc.

• The subquery within parentheses (SELECT columnX FROM source_table WHERE condition) is embedded in the WHERE clause and retrieves data from another table (source_table) based on a specific condition.

• condition is an optional condition that filters the data from the source_table before deletion.

Here's an example to illustrate how you might use a subquery with the DELETE statement:

Suppose you have two tables, employees and salaries, and you want to delete employees from the employees table whose salary exceeds the average salary in the salaries table: 

In this example, the DELETE statement removes rows from the employees table where the employee_id matches the result of the subquery. The subquery calculates the average salary from the salaries table and filters employees with salaries greater than the calculated average.