JOIN clause in SQL

JOIN Clause

The relational model allows us to execute queries on related tables, so that we can extract and integrate information from several tables in a single execution. This is possible thanks to the JOIN clause, which we will explain in more detail below:

For better understanding of the examples, we have done some new INSERT INTO on the Customers and Orders table:

INSERT INTO Customers ([name], lastName, [address], bankAccount)
VALUES ('Jessie', 'Good', '4452 Ventura Drive', 790763467);

INSERT INTO Customers ([name], lastName, [address], bankAccount)
VALUES ('Paul', 'Williams', '2696 Ocello Street', NULL);

INSERT INTO Orders (product, productDescription, price, orderDate, productQuantity, customerId)
VALUES ('Balay Oven', 'Appliance', '699.55 ', '2022-09-10', 2, NULL);

SELECT *
FROM Customers
WHERE customerId IN (6, 7);

SELECT *
FROM Orders
WHERE orderId = 8;

INNER JOIN

Is the most common JOIN .In fact if we only indicate the word JOIN in our SELECT, the system will automatically interpret it as INNER JOIN. This clause establish a relation between two tables through a common field of the main and secondary tables, returning only the records that have matching values in both tables.

The basic syntax is simple:

SELECT columns FROM table1 (INNER) JOIN table2 ON table1.relationField = table2.relationField;

The following example illustrates an INNER JOIN relationship between the Customers and Orders tables:

SELECT C.customerId,
       C.lastName,
       O.orderId,
       O.product,
       O.price,
       O.productQuantity
FROM Customers C
JOIN Orders O
ON C.customerId = O.customerId
WHERE C.name = 'Mavin';

From this execution we can know that the client with the last name Pettitt has made 2 orders, and we can also see the information of the articles.

There is an alternative way to carry out the INNER JOIN, directly indicating the tables that we want to relate in the FROM and establishing the relationship through the related fields in the WHERE.The syntax will be as it follows:

SELECT columns FROM table1, table2 WHERE table1.relationField = table2.relationField

SELECT C.customerId,
       C.lastName,
       O.orderId,
       O.product,
       O.price,
       O.productQuantity
FROM Customers C , Orders O
WHERE C.customerId = O.customerId
AND C.name = 'Mavin';

This execution would return the same result as the previous one.

If we launch an execution without WHERE filters, we can see that it returns all records whose common field (customerId) is present in both tables.

SELECT C.customerId,
       C.lastName,
       O.orderId,
       O.product,
       O.price,
       O.productQuantity
FROM Customers C
INNER JOIN Orders O
ON C.customerId = O.customerId

LEFT (OUTER) JOIN

This clause returns all the records of the left table (Customers) plus those that intersect both tables (such as the INNER JOIN).

We execute the previous statement changing the INNER JOIN clause for LEFT JOIN:

SELECT C.customerId,
       C.lastName,
       O.orderId,
       O.product,
       O.price,
       O.productQuantity
FROM Customers C
LEFT JOIN Orders O
ON C.customerId = O.customerId

RIGHT (OUTER) JOIN

This clause returns all records from the right table (Orders) plus those that intersect both tables.

We execute the previous statement using the RIGHT JOIN clause:

SELECT C.customerId,
       C.lastName,
       O.orderId,
       O.product,
       O.price,
       O.productQuantity
FROM Customers C
RIGHT JOIN Orders O
ON C.customerId = O.customerId

FULL (OUTER) JOIN

This clause returns both the left and right table records, regardless of whether they intersect or not.

We execute the previous statement with FULL JOIN:

SELECT C.customerId,
       C.lastName,
       O.orderId,
       O.product,
       O.price,
       O.productQuantity
FROM Customers C
FULL JOIN Orders O
ON C.customerId = O.customerId