SQL Joins

Best SQL Joins in 2023

by

The SQL Joins

The joins are used to combine rows from two or more database tables when there is a linked column between them in SQL. By connecting the tables using the common column, you may get the data from numerous tables in a single query.

There are various types of joins in SQL, like FULL JOIN, RIGHT JOIN, LEFT JOIN, and INNER JOIN (or LEFT OUTER JOIN) (or FULL OUTER JOIN).

I’ll describe each sql join types here with examples.

Examples of SQL Joins

  1. INNER JOIN: An INNER JOIN returns only the rows in both tables that have matching values. Let’s say you have two tables, Orders and Customers, and you want to retrieve information about orders along with the corresponding customer details.

SQL> SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

  1. LEFT JOIN (LEFT OUTER JOIN): A LEFT JOIN returns all rows from the left table (the first table mentioned) and the matched rows from the right table (the second table mentioned). If no matches are found in the right table, NULL values are returned for columns in the right table.

SQL> SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

  1. RIGHT JOIN (RIGHT OUTER JOIN): A RIGHT JOIN returns all rows from the right table (the second table mentioned) and the matched rows from the left table (the first table mentioned). If there are no matches in the left table, NULL values are returned for columns from the left table.

SQL> SELECT Orders.OrderID, Customers.CustomerName
FROM Orders
RIGHT JOIN Customers ON Orders.CustomerID = Customers.CustomerID;

  1. FULL JOIN (FULL OUTER JOIN): A FULL JOIN returns all rows from both tables, including the unmatched rows from each table. If there is no match for a row in one table, NULL values are returned for the columns of the other table.

SQL> SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

These examples above show you how to aggregate data from various tables based on their relationships using various SQL joins.

SQL Joins are essential to database queries and give you the ability to mine relational databases for insightful information.

Leave a Comment