SQL Joins Explained — INNER, LEFT, RIGHT, FULL with Examples

⭐ Want to learn SQL step-by-step? Check out our completely FREE Hands-On Practice 👉 SQL course for beginners .


Learning SQL joins is one of the most important skills for any aspiring data analyst, software developer, or database professional. Joins allow you to combine data from multiple tables, which is essential for analyzing real-world databases and generating meaningful insights.



What is a SQL Join?


📽 Watch the Video Tutorial | See Joins in Action!

A SQL join is a way to link two or more tables based on a related column between them. The purpose of a join is to extract meaningful data that exists across multiple tables in your database.



Without joins, you would need to manually combine data from different tables — which is inefficient, error-prone, and unrealistic for real-world datasets.




Types of SQL Joins



There are four primary types of SQL joins you need to master:



  • INNER JOIN


  • LEFT JOIN (or LEFT OUTER JOIN)


  • RIGHT JOIN (or RIGHT OUTER JOIN)


  • FULL JOIN (or FULL OUTER JOIN)


Each join serves a different purpose, and knowing when to use each is key to writing efficient queries.



1. INNER JOIN



The INNER JOIN returns only the rows that have matching values in both tables. It’s the most commonly used join type.



Example:



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


This query will return only customers who have placed orders. If a customer has no orders, they won’t appear in the result set.



SQL INNER JOIN Query Explained


  • SELECT Customers.CustomerID, Customers.FirstName, Orders.OrderID

    • Specifies the three columns to display in the query results.

    • • Customers.CustomerID retrieves the customer's ID.
    • • Customers.FirstName retrieves the customer's first name.
    • • Orders.OrderID retrieves the order ID.

  • FROM Customers

    • Identifies the Customers table as the starting table for the query.

  • INNER JOIN Orders

    • • Combines matching records from the Customers and Orders tables.
    • • Returns only records with matching values in both tables.

  • ON Customers.CustomerID = Orders.CustomerID

    • • Specifies the condition used to match records between the two tables.
    • • Matches customers with orders that have the same CustomerID.

  • Semicolon (;)

    • Marks the end of the SQL statement.

Remember: Customers without matching orders will not appear in the results. Customers with multiple orders will appear once for each matching order.




📽 Watch the Video Tutorial | See Inner Joins in Action!



2. LEFT JOIN



The LEFT JOIN returns all rows from the left table, and the matched rows from the right table. If there is no match, the result will show NULL for columns from the right table.



Example:



SELECT Customers.CustomerID, Customers.FirstName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;


This query returns all customers, including those who have not placed any orders.



SQL LEFT JOIN Query Explained


  • SELECT Customers.CustomerID, Customers.FirstName, Orders.OrderID

    • Specifies the three columns to display in the query results.

    • • Customers.CustomerID retrieves the customer's ID.
    • • Customers.FirstName retrieves the customer's first name.
    • • Orders.OrderID retrieves the order ID.

  • FROM Customers

    • Identifies the Customers table as the starting table for the query.

  • LEFT JOIN Orders

    • • Combines records from the Customers and Orders tables.
    • • Returns all customers, including those who have not placed any orders.
    • • Displays NULL for Orders.OrderID when a customer has no matching order.

  • ON Customers.CustomerID = Orders.CustomerID

    • • Specifies the condition used to match records between the two tables.
    • • Matches customers with orders that have the same CustomerID.

  • Semicolon (;)

    • Marks the end of the SQL statement.

Remember: A LEFT JOIN returns every customer from the Customers table, even if that customer has no matching order. Customers with multiple orders appear once for each matching order.



3. RIGHT JOIN



The RIGHT JOIN is the opposite of a LEFT JOIN. It returns all rows from the right table and the matched rows from the left table. If there is no match, the result will show NULL for columns from the left table.



Example:



SELECT Customers.CustomerID, Customers.FirstName, Orders.OrderID
FROM Customers
RIGHT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;


This query will return all orders, including those made by customers who may not exist in the Customers table (less common but possible in certain datasets).



SQL RIGHT JOIN Query Explained



  • SELECT Customers.CustomerID, Customers.FirstName, Orders.OrderID

    • Specifies the three columns to display in the query results.

    • • Customers.CustomerID retrieves the customer's ID.
    • • Customers.FirstName retrieves the customer's first name.
    • • Orders.OrderID retrieves the order ID.

  • FROM Customers

    • Identifies the Customers table as the first table in the query.

  • RIGHT JOIN Orders

    • • Combines records from the Customers and Orders tables.
    • • Returns every record from the Orders table, including orders without a matching customer record.
    • • Displays NULL for Customers.CustomerID and Customers.FirstName when an order has no matching customer.

  • ON Customers.CustomerID = Orders.CustomerID

    • • Specifies the condition used to match records between the two tables.
    • • Matches customer records with orders that have the same CustomerID.

  • Semicolon (;)

    • Marks the end of the SQL statement.

Remember: A RIGHT JOIN returns every record from the Orders table, even if no matching customer exists. Orders associated with the same customer appear as separate rows.



📽Watch the Video Tutorial | See OUTER Joins in Action!


4. FULL OUTER JOIN



The FULL JOIN returns all records from both tables, and fills in NULL where there is no match on either side.



Example:



SELECT Customers.CustomerID, Customers.FirstName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;


This query ensures that you get all customers and all orders, regardless of whether they match.


SQL FULL OUTER JOIN Query Explained


  • SELECT Customers.CustomerID, Customers.FirstName, Orders.OrderID

    • Specifies the three columns to display in the query results.

    • • Customers.CustomerID retrieves the customer's ID.
    • • Customers.FirstName retrieves the customer's first name.
    • • Orders.OrderID retrieves the order ID.

  • FROM Customers

    • Identifies the Customers table as the first table in the query.

  • FULL OUTER JOIN Orders

    • • Combines records from the Customers and Orders tables.
    • • Returns all customers and all orders, including records without a match in the other table.
    • • Displays NULL for columns from the table that has no matching record.

  • ON Customers.CustomerID = Orders.CustomerID

    • • Specifies the condition used to match records between the two tables.
    • • Matches customer records with orders that have the same CustomerID.

  • Semicolon (;)

    • Marks the end of the SQL statement.

Remember: A FULL OUTER JOIN returns matching records, customers without matching orders, and orders without matching customer records. Unmatched columns appear as NULL.



Note: After joining tables, you’ll often group results using GROUP BY.

▶ IWantToLearnSQL.com
FREE SQL VIDEO TUTORIAL
SQL INNER JOIN Video Tutorial
Watch: SQL INNER JOINS Explained

Learn how to create an inner join with practical examples. Follow along as we explain the queries step by step.

▶ WATCH THE FREE VIDEO


Visualizing SQL Joins



Understanding joins conceptually can be easier with a diagram:



  • INNER JOIN: Intersection of both tables


  • LEFT JOIN: All of left table + intersection


  • RIGHT JOIN: All of right table + intersection


  • FULL OUTER JOIN: Union of all rows from both tables


Common Mistakes When Using Joins



• Even experienced SQL users make mistakes with joins. Avoid these:



  • • Forgetting to include the ON condition


  • • Using the wrong type of join for the business requirement


  • • Assuming LEFT JOIN = INNER JOIN (results will differ)


  • • Joining on the wrong column


  • • Not handling NULL values properly


Best Practices for SQL Joins



  • • Always specify the join condition with ON


  • • Use table aliases to make queries easier to read


  • • Start with INNER JOINs unless you specifically need LEFT, RIGHT, or FULL JOINs


  • • Test queries with small datasets first


  • • Use explicit joins rather than comma-separated joins for clarity


Practice Exercises



Here are exercises you can try to master SQL joins:



  1. List all customers who have placed orders.


  2. List all customers and show NULL for customers who haven’t placed any orders.


  3. List all orders, including orders without a matching customer record.


  4. Combine all customers and all orders in a full outer join.


Try solving these in a local SQL environment.



Conclusion



SQL joins are essential for combining data across multiple tables, performing analysis, and creating reports. Mastering INNER, LEFT, RIGHT, and FULL JOINs will give you a strong foundation for any SQL-related career.



Remember, practice is key. Apply these joins to real datasets, experiment with different scenarios, and check your results carefully.



Continue Learning SQL