DBMS - Join and Subquery in SQL

In SQL querying, we sometimes need to fetch data from more than one table, for which we need to join the tables together or apply subqueries. Subqueries and the Join operation help us retrieve related data by linking the tables or running nested queries within a larger query.

In this chapter, we will have an elaborate discussion on joins and subqueries with the help of clear examples and their resulting tables.

Sample Tables and Data

For the examples in this chapter, we will use the following three tables: Customers, Orders, and Products. We will use these three tables to demonstrate joins and subqueries.

Here is the Customers table −

This is the Products table −

And, the Orders table is as follows −

Joins in SQL

Joins combine rows from two or more tables based on a related column. In SQL we have option for several types of joins. We will see the most commonly used techniques.

The Inner Join

The Inner Join retrieves rows that have matching values in both tables. If some matching is not there the entire row will be neglected.

Example: Fetch Customer Orders

Use the following query to fetch all the customer orders −

SELECT c.first_name, c.last_name, p.product_name, o.quantity, o.order_date
FROM Orders o
JOIN Customers c ON o.customer_id = c.customer_id
JOIN Products p ON o.product_id = p.product_id;

This query combines the Orders, Customers, and Products tables to display complete order details −

The Left Join

The Left Join retrieves all the rows from the left table and the matching rows from the right table. If no match exists, NULL values are returned for the right table's columns.

Example: Customers with or without Orders

Use the following query to fetch the customer details with or without orders −

SELECT c.first_name, c.last_name, o.order_id
FROM Customers c
LEFT JOIN Orders o ON c.customer_id = o.customer_id;

It fetches all customers, including those with no orders (if there were any).

The Right Join

The Right Join retrieves all the rows from the right table and the matching rows from the left table.

Example: Products with or without Orders

Use the following query to fetch the products with or without orders −

SELECT p.product_name, o.order_id
FROM Products p
RIGHT JOIN Orders o ON p.product_id = o.product_id;

The query fetches all the rows from the Orders table. When it is a product that does not match, it will be NULL. All products are matched in this case.

The Full Outer Join

Combining both the Left and Right join makes the new join a Full Outer Join. It will fetch all the rows from both the tables.

Subqueries in SQL

Subqueries are nested queries inside another query. They can return single values or entire tables. Subqueries are useful for solving complex problems step-by-step.

Subqueries in WHERE Clause

Subqueries are mostly used in the WHERE clause to filter data based on the result of another query.

Example: Find Customers Who Ordered the Most Expensive Product

Here, we have three queries all together. The innermost query finds the product_id having the maximum number of products. Then, this product_id is matched with the Orders table in the second inner query, from where it gets the customer_id. Based on the customer_id, it fetches the name of the customer.

SELECT first_name, last_name
FROM Customers
WHERE customer_id IN (
   SELECT customer_id
   FROM Orders
   WHERE product_id = (
      SELECT product_id
      FROM Products
      WHERE price = (SELECT MAX(price) FROM Products)
   )
);

This query identifies the customers who ordered the most expensive product (Laptop).

Subqueries in SELECT Clause

Subqueries can also be used in the SELECT clause to include calculated data in the output.

Example: Total Quantity Ordered by Each Product

Use the following query to get the details of the total quantity ordered by each product −

SELECT product_name,
   (SELECT SUM(quantity)
   FROM Orders
   WHERE Orders.product_id = Products.product_id) AS total_quantity_ordered
FROM Products;

This query calculates the total quantity ordered for each product using a subquery. It is an advanced version of GROUP BY querying.

Subqueries in FROM Clause

Subqueries in the FROM clause are called derived tables and are used to create temporary tables.

Example: Find Revenue per Product

Use the following query to get the revenue details per product −

SELECT product_name, revenue
FROM (
   SELECT p.product_name, SUM(o.quantity * p.price) AS revenue
   FROM Orders o
   JOIN Products p ON o.product_id = p.product_id
   GROUP BY p.product_name
) AS ProductRevenue;

The subquery calculates revenue per product, and the main query fetches it for display.

When to Use Joins vs. Subqueries

Both the approaches, joins and subqueries, are equally useful and often interchangeable. However, the joining operation is avoided sometimes due to its resulting large intermediate table size. In such cases, using an inner query is much more efficient. But, choosing the right option depends on the situation.

• Use Joins when you need to combine rows from multiple tables directly.
• Use Subqueries when you need to break down a problem into steps or filter the data based on calculated values.

Conclusion

To conclude, joins and subqueries are used in SQL to combine and analyze data from multiple tables. We explained in detail how joins like INNER JOIN, LEFT JOIN, and RIGHT JOIN link tables, and how subqueries like WHERE, SELECT, and FROM clauses make complex queries manageable.