SQL Joins Explained

SQL Joins are utilized to fetch data from multiple tables based on logical relations between them. This merging of tables allows for a more comprehensive dataset derived from multiple sources. Different types of joins determine which records end up in the merged result.

JOIN (Typically INNER JOIN)

The JOIN keyword, when used by itself, is equivalent to INNER JOIN in most databases. It selects records that have matching values in both tables.

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

INNER JOIN

The INNER JOIN keyword selects records that have matching values in both tables.

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

LEFT JOIN (or LEFT OUTER JOIN)

The LEFT JOIN keyword returns all records from the left table, and the matched records from the right table. The result is NULL from the right side, if there is no match.

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

RIGHT JOIN (or RIGHT OUTER JOIN)

The RIGHT JOIN keyword returns all records from the right table, and the matched records from the left table. The result is NULL from the left side, if there is no match.

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

FULL JOIN (or FULL OUTER JOIN)

The FULL JOIN keyword returns all records when there is a match in one of the tables. Hence, it returns all the rows from the left table and all the rows from the right table.

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

While these are the most common types of joins, it's worth noting that the exact availability and behavior can vary depending on the database system. Some databases might not support all these types or may have additional types or variations.