joins

SQL Joins:

SQL joins are used to query data from two or more tables, based on a relationship between certain columns in these tables.

Different SQL JOINs

Before we continue with examples, we will list the types of JOIN you can use, and the differences between them.

• JOIN: Return rows when there is at least one match in both tables
• LEFT JOIN: Return all rows from the left table, even if there are no matches in the right table
• RIGHT JOIN: Return all rows from the right table, even if there are no matches in the left table
• FULL JOIN: Return rows when there is a match in one of the tables

SQL INNER JOIN :

The INNER JOIN keyword return rows when there is at least one match in both tables.

SQL INNER JOIN Syntax

SELECT column_name(s) FROM table_name1 INNER JOIN table_name2 ON table_name1.column_name=table_name2.column_name

PS: INNER JOIN is the same as JOIN.

---

SQL INNER JOIN Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

The "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

Now we want to list all the persons with any orders.
We use the following SELECT statement:

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

The result-set will look like this:

LastName	FirstName	OrderNo
Hansen	Ola	22456
Hansen	Ola	24562
Pettersen	Kari	77895
Pettersen	Kari	44678

The
INNER JOIN keyword return rows when there is at least one match in both
tables. If there are rows in "Persons" that do not have matches in
"Orders", those rows will NOT be listed.

SQL LEFT JOIN:

The
LEFT JOIN keyword returns all rows from the left table (table_name1),
even if there are no matches in the right table (table_name2).

SQL LEFT JOIN Syntax

SELECT column_name(s) FROM table_name1 LEFT JOIN table_name2 ON table_name1.column_name=table_name2.column_name

PS: In some databases LEFT JOIN is called LEFT OUTER JOIN.

---

SQL LEFT JOIN Example

The "Persons" table:

P_Id	LastName	FirstName	Address	City
1	Hansen	Ola	Timoteivn 10	Sandnes
2	Svendson	Tove	Borgvn 23	Sandnes
3	Pettersen	Kari	Storgt 20	Stavanger

The "Orders" table:

O_Id	OrderNo	P_Id
1	77895	3
2	44678	3
3	22456	1
4	24562	1
5	34764	15

Now we want to list all the persons and their orders - if any, from the tables above.
We use the following SELECT statement:

SELECT Persons.LastName, Persons.FirstName, Orders.OrderNo FROM Persons LEFT JOIN Orders ON Persons.P_Id=Orders.P_Id ORDER BY Persons.LastName

The result-set will look like this:

LastName	FirstName	OrderNo
Hansen	Ola