SQL JOIN

SQL join is used to query data from these tables based on the relationship between columns in two or more tables.

Join and Key

Sometimes, to get a complete result, we need to retrieve results from two or more tables. We need to perform a join.

Tables in the database can be linked to each other through keys. A primary key (Primary Key) is a column in which each row has a unique value. In the table, each primary key value is unique. The purpose of this is to cross-link data between tables without repeating all the data in each table.

Let's look at the "Persons" table:

Id_P LastName FirstName Address City
1 Adams John Oxford Street London
2 Bush George Fifth Avenue New York
3 Carter Thomas Changan Street Beijing

Please note that the "Id_P" column is the primary key in the Persons table. This means that no two rows can have the same Id_P. Even if two people have the same name, Id_P can distinguish them.

Next, let's look at the "Orders" table:

Id_O OrderNo Id_P
1 77895 3
2 44678 3
3 22456 1
4 24562 1
5 34764 65

Please note that the "Id_O" column is the primary key in the Orders table, and the "Id_P" column in the "Orders" table is used to reference the "Persons" table without using their exact names.

Please note that the "Id_P" column links the two tables above together.

Referencing two tables

We can retrieve data from two tables by referencing the two tables:

Who ordered the product and what product did they order?

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons, Orders
WHERE Persons.Id_P = Orders.Id_P

Result Set:

LastName FirstName OrderNo
Adams John 22456
Adams John 24562
Carter Thomas 77895
Carter Thomas 44678

SQL JOIN - Using Join

In addition to the above method, we can also use the keyword JOIN to retrieve data from two tables.

If we want to list the orders of all people, we can use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo
FROM Persons
INNER JOIN Orders
ON Persons.Id_P = Orders.Id_P
ORDER BY Persons.LastName

Result Set:

LastName FirstName OrderNo
Adams John 22456
Adams John 24562
Carter Thomas 77895
Carter Thomas 44678

Different SQL JOINs

In addition to the INNER JOIN (inner join) used in the example above, we can also use other types of joins.

Below is a list of JOIN types you can use, as well as the differences between them.

  • JOIN: Returns rows if there is at least one match in the table
  • LEFT JOIN: Returns all rows from the left table even if there is no match in the right table
  • RIGHT JOIN: Returns all rows from the right table even if there is no match in the left table
  • FULL JOIN: Returns rows if there is a match in either table