Contact Us

Kockpit is here to help you

Business Form

Phani Krishna Karri

Mastering SQL Joins and Sub-Queries

Structured Query Language (SQL) is the main asset of database management and data retrieval. Whether you're a beginner or an experienced database developer, understanding SQL Joins and Sub Queries is essential to unlock the full potential of relational databases. In this article, we'll deeply discuss the SQL concepts to help you explore in this Article. 

Understanding of SQL Joins

SQL Joins is used to combine data from two or more related tables into a single result set (or) table. This enables you to retrieve and analyze data stored across multiple tables, creating valuable output.

There are four primary types of SQL joins:

Inner Join

An INNER JOIN returns rows that have matching values in both tables being joined. It filters out rows that do not have corresponding records in both tables.

EX: - 1 


EX: - 2 

Query Syntax: 

Select Column Name From Table 1 Inner Join Table 2

ON < Joining Condition >;

Example Query:  

Select E.EMP_ID, E.EMP_Name, OD.Order_ID, OD.Amount 

From Employee as E Inner Join Order_Details as OD

ON E.EMP_ID = OD.EMP_ID;

Output:

Summary: Inner Join gives only the matched records, not all the records.

Outer Join

An OUTER JOIN in SQL is a type of join operation that retrieves data from two or more tables based on a specified condition, unlike an inner join. It is also obtain matched along with unmatched records.

There are 3 types of OUTER JOIN: 

Right Outer Join

This RIGHT OUTER JOIN returns all rows from the right table and the matching rows from the left table. If there is no match for a row from the right table in the left table, the result will contain NULL values for columns from the left table. 

EX: - 1

EX: - 2

Query Syntax: 

Select Column Name From Table 1 Right Join Table 2

ON < Joining Condition >;

Example Query:

Select E.EMP_ID, E.EMP_Name, OD.Order_ID, OD.Amount 

From Employee as E Right Join Order_Details as OD

ON E.EMP_ID = OD.EMP_ID;

Output:

Summary: Right Outer Join gives all the data from the Right table and matched records from the left table.

Left Outer Join

This LEFT OUTER JOIN returns all rows from the Left table and the matching rows from the Right table. If there is no match for a row from the left table in the Right table, the result will contain NULL values for columns from the Right table.

EX: - 1

EX: - 2

 Query Syntax:

Select Column Name From Table 1 Left Join Table 2

ON < Joining Condition >;

Example Query:

Select E.EMP_ID, E.EMP_Name, OD.Order_ID, OD.Amount 

From Employee as E Left Join Order_Details as OD

ON E.EMP_ID = OD.EMP_ID;

Output:

Summary: Left Outer Join will give total records from the left side and matched from the right side.

Full Outer Join

This FULL OUTER JOIN combines the result sets of both a left join and a right join. It returns all rows from both the left table and the right table, including matching rows and non-matching rows from both tables. If there's no match for a row in one table, it will still appear in the result set with NULL values for columns from the other table.

EX: - 1

EX: - 2

Query Syntax: 

Select Column Name From Table 1 Full Join Table 

ON < Joining Condition >  Order by Column Name;

Example Query:

Select E.EMP_ID, E.EMP_Name, OD.Order_ID, OD.Amount 

From Employee as E Full Join Order_Details as OD

ON E.EMP_ID = OD.EMP_ID   Order by EMP_ID;

Output:

Summary: Full outer Join will give all the records from both tables.

Cross Join

A CROSS JOIN also known as a Cartesian Join. It returns the Cartesian product of two tables, which means it combines every row from the first table with every row from the second table. It doesn't require a specific condition for matching rows because it combines all possible combinations.

EX: - 1


EX: - 2

Query Syntax:

Select Column Name From Table 1 Cross Join Table 2;

Example Query:

Select E.EMP_ID, E.EMP_Name, E.EMP_ID, OD.Order_ID,

OD.Amount From Employee as E Cross Join 

Order_Details as OD;

Output:

Summary:  Cross Join will combine all rows from two tables, creating a Cartesian product, resulting in m * n rows.

Self Join

SELF-JOIN is nothing like joining the table with itself. Whenever the data is to be selected in the same table but in a different Row. When dealing with hierarchical data we will go for self-join.

EX: - 1


EX: - 2

Query Syntax:

Select T1.Column Name, T2. Column Name

From Table T1 Join Table T2

On < Joining Condition >;

Example Query:

Select T1.EMP_ID, T1.EMP_Name, T2.EMP_Name as 

Reporting Manager From Employee T1 Join Employee T2

On T1.EMP_ID=T2.EMP_ID;

Output:

Summary: Self Join will join by itself to establish a relation with that table

Now we have learned the importance of Mastering SQL Joins is crucial for combining and extracting data from multiple tables in databases, enabling better data analysis and reporting. Continuous learning and practice are essential to become proficient in using joins effectively and making informed decisions based on relational data.


Understanding Of SQL Sub-Queries

SUB-QUERY is part of the SQL and it also known as nested queries or inner queries, allows you to retrieve, update, or manipulate data based on the results of another query. Subqueries can appear in various parts of a SQL statement, including the WHERE clause, FROM clause, or even the SELECT clause, depending on their purpose. The executed outer query returns a single value, a single row, or a set of rows that can be used to filter or perform calculations on the data.

Types of Sub-Queries: 

Subqueries can be categorized into several types based on their purpose and situation:

Scalar Subqueries

A scalar subquery returns a single value and is often used in the SELECT clause or WHERE clause to compare values or perform calculations. 

Example: You can use a scalar subquery to find the maximum salary in a department or retrieve the count of records that match the output criteria.

Example Query:

SELECT employee_name, salary

FROM employees

WHERE salary = (SELECT MAX(salary) FROM employees

WHERE department_id = 101); 

Row Subqueries

Row subqueries return a single row and are typically used in the WHERE clause to filter data based on a condition that involves multiple columns. They are useful when you need to compare an entire row from one table with the results of a subquery.

Example: You can use the Row subquery to find out which products_id and category_id are in the Discount products.

Example Query:

SELECT *

FROM products

WHERE (product_id, category_id) IN (SELECT product_id, category_id FROM discount_products);


Table Subqueries

Table subqueries return multiple rows and are used in the FROM clause to create temporary tables that can be joined with other tables in the main query. This is useful for complex data retrieval and transformation tasks.

Example Query:

SELECT E.employee_name, D.department_name

FROM (SELECT employee_id, employee_name, department_id FROM employees) AS E

JOIN departments AS D ON E.department_id = D.department_id;

Now we have learned about three types of SQL SUB-Quries, and we've seen how to use them with some examples above. Keep practicing by applying these subqueries to filter the data in your SQL Queries.


Common Use Cases for Subqueries

Sub-queries are used in various scenarios based on the situation we use them. There are some commonly used subqueries:

Filtering the Data

Subqueries can be used to filter records based on certain conditions. For instance, you can retrieve all customers who have made more than one purchase.

Example Query:

SELECT customer_name

FROM customers

WHERE customer_id IN (SELECT customer_id FROM orders GROUP BY customer_id HAVING COUNT(*) > 1);

Calculations

Subqueries help perform calculations on data. You can use them to compute aggregates, such as finding the average salary of employees in a department. 

Example Query:

SELECT department_name, (SELECT AVG(salary) FROM employees WHERE department_id = D.department_id) AS avg_salary FROM departments AS D;


Data Modification

Subqueries can also be used in UPDATE and DELETE statements to modify or remove records based on specific conditions.

Example: You can update the prices of products in a category that meets certain criteria

Example Query:

UPDATE products

SET price = price * 0.9

WHERE category_id = (SELECT category_id FROM categories WHERE category_name = 'Electronics');


Now, we have learned the importance of mastering subqueries is an essential skill for those who want to become SQL Developers and data analysts. It allows for data retrieval and manipulation, making it possible to derive valuable insights from complex datasets. By understanding the types of subqueries, optimizing performance, and practicing regularly, you can reach the full potential of SQL and take your Data Analysis skills to the next level.

Conclusion

Finally! SQL joins are used to combine data from multiple tables based on a common column, allowing us to retrieve related information. SQL sub-queries, on the other hand, are used to filter and manipulate data within a query. Both joins and sub-queries are important tools for working with data in SQL, and they offer different ways to get the information you need.