In SQL, joins are used to combine records from two or more tables based on a related column between them. They help to bring data together, making it possible to query data from multiple tables in a relational database.

There are different types of joins that are commonly used, each with its own purpose and behavior. These include:

• INNER JOIN
• OUTER JOIN
• LEFT JOIN
• RIGHT JOIN

1. INNER JOIN

The INNER JOIN returns only those records that have matching values in both tables. It excludes rows that do not have a match in one of the tables.

Usage: Use INNER JOIN when you want to select only the rows that have corresponding matches in both tables.

Syntax:

    SELECT column_name(s)
    FROM table1
    INNER JOIN table2
    ON table1.common_column = table2.common_column;
    

Example: Get the list of customers who have placed orders.

    SELECT customers.customer_id, customers.customer_name, orders.order_id
    FROM customers
    INNER JOIN orders ON customers.customer_id = orders.customer_id;
    

Performance Consideration: INNER JOIN can be efficient when tables have a strong relationship through foreign keys. However, it may perform poorly if the dataset is large and the join condition is complex.

2. OUTER JOIN

An OUTER JOIN returns all records when there is a match in either left or right table records. There are two types of OUTER JOIN:

• LEFT JOIN (or LEFT OUTER JOIN)
• RIGHT JOIN (or RIGHT OUTER JOIN)

Usage: Use OUTER JOIN when you want to retain all rows from one or both tables, even if there is no match between them.

3. LEFT JOIN

The LEFT JOIN returns all records from the left table and the matched records from the right table. If there is no match, the result is NULL on the side of the right table.

Syntax:

    SELECT column_name(s)
    FROM table1
    LEFT JOIN table2
    ON table1.common_column = table2.common_column;
    

Example: Retrieve all customers and their orders, including customers who have not placed any orders.

    SELECT customers.customer_id, customers.customer_name, orders.order_id
    FROM customers
    LEFT JOIN orders ON customers.customer_id = orders.customer_id;
    

Performance Consideration: LEFT JOIN is generally used when the left table has many rows that should be retained even if there are no corresponding records in the right table. It may be slower than INNER JOIN because it has to retain unmatched rows.

4. RIGHT JOIN

The RIGHT JOIN returns all records from the right table and the matched records from the left table. If there is no match, the result is NULL on the side of the left table.

Syntax:

    SELECT column_name(s)
    FROM table1
    RIGHT JOIN table2
    ON table1.common_column = table2.common_column;
    

Example: Retrieve all orders and the customers who placed them, including orders that do not have associated customer details.

    SELECT customers.customer_id, customers.customer_name, orders.order_id
    FROM customers
    RIGHT JOIN orders ON customers.customer_id = orders.customer_id;
    

Performance Consideration: RIGHT JOIN can be useful when all rows from the right table are required, even if there are no matching rows in the left table. Similar to LEFT JOIN, it can be slower than INNER JOIN.

5. FULL OUTER JOIN

The FULL OUTER JOIN returns all records when there is a match in either left or right table records. This join combines the result of LEFT JOIN and RIGHT JOIN, returning rows that have matches in both tables and rows with unmatched data in either table.

Syntax:

    SELECT column_name(s)
    FROM table1
    FULL OUTER JOIN table2
    ON table1.common_column = table2.common_column;
    

Example: Get all customers and all orders, including those that do not have a corresponding match in the other table.

    SELECT customers.customer_id, customers.customer_name, orders.order_id
    FROM customers
    FULL OUTER JOIN orders ON customers.customer_id = orders.customer_id;
    

Performance Consideration: FULL OUTER JOIN can be resource-intensive and slower because it has to combine unmatched records from both tables. It is less commonly used than other joins.

Conclusion

Joins are essential tools in SQL for combining data from multiple tables. Choosing the right type of join—whether it’s INNER JOIN, LEFT JOIN, RIGHT JOIN, or FULL OUTER JOIN—depends on the data relationship and the specific requirements of your query. Understanding their differences helps in writing efficient SQL queries and achieving the desired results from relational databases.

Special thanks to Gauri Tomar for contributing to this article on takeUforward. If you also wish to share your knowledge with the takeUforward fam, please check out this article.