Introduction SQL JOINs are used to combine data from two or more tables into a single result based on a shared column. They allow you to retrieve related data that is stored separately. customer names in one table and purchase details in another. A Join bridges the tables, giving insights that one table alone may not provide. In this article, we’ll break down the different types of SQL JOINs and when to use them. Specifically, we’ll cover: INNER JOIN LEFT JOIN RIGHT JOIN FULL OUTER JOIN CROSS JOIN SELF JOIN An INNER JOIN combines records based on a related column and returns only matching rows from both tables. Syntax SELECT columns FROM table1 INNER JOIN table2 ON table1.column = table2.column; Example; If the customers table and the orders table share a column - the customer_id, an INNER JOIN is used to identify the customers who made orders; SELECT customers.customer_id, first_name, last_name, order_id FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id; Result; customer_id first_name last_name order_id 1 john doe 1 2 mary wanjiku 2 3 peter otieno 3 1 john doe 4 4 lucy njeri 5 An INNER JOIN can be used to combine more than two tables. For example, to find out the customers, books they purchased and the corresponding order IDs; SELECT orders.order_id, first_name, last_name, title FROM customers INNER JOIN orders ON customers.customer_id = orders.customer_id INNER JOIN books b ON books.book_id = orders.book_id; Result; order_id first_name last_name title 1 john doe Learning SQL 2 mary wanjiku Data Analytics Basics 3 peter otieno Python for Data Science 4 john doe Data Analytics Basics 5 lucy njeri Advanced SQL Queries The LEFT JOIN returns all records from the left table, and the matching records from the right table. Where no match exists, a NULL value is returned. In a LEFT JOIN, the table specified in the FROM clause is treated as the left table. Syntax SELECT columns FROM table1 LEFT JOIN table2 ON table1.column = table2.column; To show all the books and the customers who ordered them, a LEFT JOIN is used; SELECT title,first_name,order_id FROM books LEFT JOIN orders ON books.book_id = orders.book_id LEFT JOIN customers ON orders.customer_id = customers.customer_id; Result; title first_name order_id Learning SQL john 1 Data Analytics Basics mary 2 Python for Data Science peter 3 Data Analytics Basics john 4 Advanced SQL Queries lucy 5 Machine Learning Intro NULL NULL Notice that the row with Machine Learning Intro has NULL values because it was not ordered. The RIGHT JOIN works like the LEFT JOIN, but it keeps all the rows from the right table and matching rows from the left table. Syntax; SELECT columns FROM table1 RIGHT JOIN table2 ON table1.column = table2.column; To show every book in the library, whether it was ordered or not; SELECT books.book_id, title, order_id FROM orders RIGHT JOIN books ON books.book_id = orders.book_id; Result; book_id title order_id 1 Learning SQL 1 2 Data Analytics Basics 2 3 Python for Data Science 3 2 Data Analytics Basics 4 4 Advanced SQL Queries 5 5 Machine Learning Intro NULL ⚠️ Note: For both LEFT and RIGHT outer joins, the order in which you write your tables determines which one returns all of its values. You need to be keen about which table you put on the left and which goes on the right to get your intended result. A FULL JOIN returns all rows from both tables. NULL is returned. Syntax; SELECT columns FROM table1 FULL OUTER JOIN table2 ON table1.column = table2.column; To retrieve all the customers and all the books, whether ordered or not, a FULL JOIN will be used; SELECT order_id, first_name, last_name, title FROM orders FULL JOIN customers ON customers.customer_id = orders.customer_id FULL JOIN books ON orders.book_id = books.book_id; Result; order_id first_name last_name title 1 john doe Learning SQL 2 peter otieno Python for Data Science 3 lucy njeri Advanced SQL Queries 2 mary wanjiku Data Analytics Basics NULL david kimani NULL NULL NULL NULL Machine Learning Intro To retrieve only the matched or un-matched records, you can filter with the WHERE clause. A CROSS JOIN returns the combination of each row from one table with each row from another table. Syntax; SELECT * FROM table1 CROSS JOIN table2; A CROSS JOIN does not have an ON clause, because we are not looking for matching rows. If you have 2 tables; t-shirts color and t-shirt size tables, and wanted to find all possible combinations, a CROSS JOIN is used. SELECT tshirt_colors.color_name, tshirt_sizes.size_name FROM tshirt_colors CROSS JOIN tshirt_sizes; Result; color_name size_name Black Small Black Medium Black Large Black Extra Large White Small White Medium White Large White Extra Large Navy Small Navy Medium Navy Large Navy Extra Large A SELF JOIN is used when comparing rows within the same table. When using self joins, it is essential to use Aliases, to distinguish the two "roles" of the same table for exact execution. AS is used to introduce an alias Syntax; SELECT column name (s) as alias FROM employees A JOIN employees B ON A.column = B.column; To best illustrate self joins, we will use the employees table below; employee_id name department_id manager_id salary 1 Alice 1 NULL 50000 2 Bob 2 1 45000 3 Charlie 1 1 47000 4 Diana 3 NULL 60000 5 Eve NULL NULL 40000 6 Brian 2 4 50000 7 Joy 3 4 35000 8 Luke 5 1 45000 To show employees and their managers, we use a SELF JOIN as follows; SELECT e.name AS employee, m.name AS manager FROM employees e JOIN employees m ON e.manager_id = m.employee_id; Result; employee manager Bob Alice Charlie Alice Brian Diana Joy Diana Luke Alice I hope this guide made joins less intimidating! save it for later or share it with a friend who's just starting their SQL journey! Happy learning!! Are there any joins I left out? Do you have any join tricks you want to share? Let me know in the comment section below, I’d love to hear from you!