What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

on

 

In SQL, INNER JOIN, LEFT JOIN (or LEFT OUTER JOIN), RIGHT JOIN (or RIGHT OUTER JOIN), and FULL JOIN (or FULL OUTER JOIN) are different types of joins used to combine data from multiple tables based on a specified condition. Here's an explanation of each type of join with examples:

  1. INNER JOIN:

    An INNER JOIN returns only the rows that have matching values in both tables.

    Example: Suppose you have two tables, Customers and Orders, and you want to retrieve all orders along with customer information for customers who have placed orders.

    sql
  • SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

  • LEFT JOIN (or LEFT OUTER JOIN):

    A LEFT JOIN returns all rows from the left table (the first table mentioned) and the matching rows from the right table (the second table mentioned). If there are no matching rows in the right table, it returns NULL values for the columns from the right table.

    Example: Suppose you have two tables, Employees and Orders, and you want to retrieve all employees along with their orders, even if some employees haven't placed any orders.

    sql
  • SELECT Employees.EmployeeName, Orders.OrderID
FROM Employees
LEFT JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID;

  • RIGHT JOIN (or RIGHT OUTER JOIN):

    A RIGHT JOIN is similar to a LEFT JOIN but returns all rows from the right table and the matching rows from the left table. If there are no matching rows in the left table, it returns NULL values for the columns from the left table.

    Example: Suppose you have two tables, Orders and Customers, and you want to retrieve all orders along with customer information, even if some orders are not associated with customers.

    sql
  • SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
RIGHT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

  • FULL JOIN (or FULL OUTER JOIN):

    A FULL JOIN returns all rows when there is a match in either the left or right table. It combines the results of both the LEFT JOIN and RIGHT JOIN. If there is no match, it returns NULL values for the columns from the table where there is no match.

    Example: Suppose you have two tables, Employees and Orders, and you want to retrieve all employees along with their orders, including employees with no orders and orders with no associated employees.

    sql
    1. SELECT Employees.EmployeeName, Orders.OrderID
FROM Employees
FULL JOIN Orders ON Employees.EmployeeID = Orders.EmployeeID;

    These are the main types of joins in SQL, and they allow you to combine data from multiple tables in different ways based on your specific requirements.

    Comments

    Post a Comment