What is a JOIN in SQL?

Nadim Muhammad

Feb 23,2025 - 23:06

A JOIN in SQL is used to combine records from two or more tables based on a related column between them. It allows retrieving data from multiple tables in a meaningful way.

Types of SQL Joins with Description:

1.  INNER JOIN

• Returns only the records that have matching values in both tables.
• If there is no match, the record is excluded.

  INNER JOIN Example

  Tables Used

  We have two tables:

• 

             SELECT Customers.CustomerID, Customers.Name, Orders.OrderID, Orders.Product
             FROM Customers
             INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

• Only matching records (CustomerID 1, 2, and 3) are included.
• CustomerID 4 (David) is excluded because there is no matching order.
• CustomerID 5 from the Orders table is also excluded because it has no match in the Customers table.

2. LEFT JOIN (LEFT OUTER JOIN)

• Description: Returns all records from the left table (table1), and the matched records from the right table (table2). If no match is found, the result is NULL on the right side.

• Use Case: When you want all records from the left table, and matching records from the right table, even if there is no match.

• Syntax:

  SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.column;

• Example: If you want to get all customers and their orders, including those who have not placed an order:

  SELECT Customers.CustomerName, Orders.Product FROM Customers LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

3. RIGHT JOIN (RIGHT OUTER JOIN)

• Description: Returns all records from the right table (table2), and the matched records from the left table (table1). If no match is found, the result is NULL on the left side.

• Use Case: When you want all records from the right table, and matching records from the left table, even if there is no match.

• Syntax:

  SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column = table2.column;

• Example: If you want to get all orders and their corresponding customers, even if there is no customer:

  SELECT Customers.CustomerName, Orders.Product FROM Customers RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

• Result: Even orders without a customer will be included, with NULL in the customer columns

• 4. FULL JOIN (FULL OUTER JOIN)

• Description: Returns all records when there is a match in either the left (table1) or right (table2) table. If there is no match, the result will contain NULL for the missing side.

• Use Case: When you want all records from both tables, whether or not they match.

• Syntax:

• SELECT columns
  FROM table1
  FULL JOIN table2
  ON table1.column = table2.column;

• Example: If you want to get a list of all customers and all orders, even if a customer has not placed an order, or an order has no customer:

• SELECT Customers.CustomerName, Orders.Product
  FROM Customers
  FULL JOIN Orders
  ON Customers.CustomerID = Orders.CustomerID;

• Result: Includes all customers, all orders, and NULL values where no match exists.

• 5. CROSS JOIN

• Description: Returns the Cartesian product of both tables. It combines every row from the first table with every row from the second table, resulting in a large number of rows (the total number of rows is the product of the number of rows in each table).

• Use Case: Rarely used, but can be useful when you need to generate all combinations of records from two tables.

• Syntax:

• SELECT columns FROM table1 CROSS JOIN table2;

• Example: If you want to combine each customer with each product in your store:

• SELECT Customers.CustomerName, Products.ProductName
  FROM Customers
  CROSS JOIN Products;