Types of JOINs in SQL

An important component of any relational database system is the relationship between tables. This relationship facilitates connecting two tables based on data that they ‘have in common’. This connection is accomplished using a JOIN, which is an operation that matches rows from one table to the rows in another. The matching is done in such a manner that the columns from both the tables placed side by side although they may have come from separate tables. It must be said though, the fewer tables being joined is the faster the query will operate. This article gives an introduction to joins and their uses in the relational database engine.

Let’s create tables: CUSTOMERS and ORDERS as follows:

CUSTOMERS ( cust_id [PK] , cust_fname, cust_lname, phone, address, country);

ORDERS ( order_id [PK] , order_date, reqd_date, ship_date, cust_id [FK] );

INNER JOIN

Scenario: We need to see a list of customers and details of their respective orders.

This is the simplest type of join where each row in one table is matched with all other rows in another table. Only if the join condition evaluates to true a row is. The data in common, must be true for both tables involved in the JOIN.

SELECT 
    customers.cust_fname, orders.order_id, orders.order_date 
FROM 
    customers INNER JOIN orders ON customer.cust_id = orders.cust_id; 

Interestingly enough (doesn’t work for all engines), you can also write an INNER JOIN query with a WHERE condition.

SELECT 
    customers.cust_fname, orders.order_id, orders.order_date 
FROM 
    customers, orders  
WHERE  
    customers.cust_id = orders.cust_id;

LEFT JOIN

This JOIN retrieves all records in the table that is on the (literally) LEFT side of the join condition AND…any columns that match from the table on the RIGHT side of the condition. So a so customer with no orders will be returned from the customer table, but NULL values are returned for the columns in the orders table for the rows that do not match the JOIN condition.

SELECT 
    customers.cust_fname, orders.order_id, orders.order_date 
FROM 
    customers LEFT JOIN orders 
ON customer.cust_id = orders.cust_id;

RIGHT JOIN

This JOIN does the inverse of the LEFT JOIN. It returns all records from the table on the right, with any rows that might match the condition from the left table. In the same way, NULL values are returned for the columns in the left table for the rows that do not match the JOIN condition.

SELECT 
    customers.cust_fname, orders.order_id, orders.order_date 
FROM 
    customers RIGHT JOIN orders 
ON customer.cust_id = orders.cust_id;

CROSS JOIN

A cross join returns as many combinations of all the rows contained in the joined tables. That is,each row of the left table concatenated with every row in the right table. Therefore, if there are total M rows in Table A and there are N rows in Table B, a cross join will produce M x N rows. Two ways to accomplish this CROSS JOIN are as follows:

CROSS JOIN

SELECT 
    customers.cust_fname, orders.order_id, orders.order_date 
FROM customers CROSS JOIN orders;

SELECT Statement

SELECT 
    customers.cust_fname, orders.order_id, orders.order_date 
FROM 
    customers, orders;

Conclusion

Typically, JOINs are used to establish a master-detail kind of data representation and are essential for pulling meaningful data from multiple related tables. Multiple tables can be joined in a singular select statement, but remember that the query will slow down for each table and more so, the number of records being returned.

#sql #databases