SQL guide-join

Document directory

• Example
• Internal Connection example
• Left join example
• Right join example
• Example

Joins and keys

In some cases, we have to select two or more tables to return results. We have to execute a connection. The tables in the database involve the mutual keys. The primary key is the unique value of each row of the column. The purpose is to bind data together. There is no duplicate data in each table across tables.

In the following "employees" table, the "employee_id" column is the primary key, meaning there are no identical two rows of employee_id. The employee ID can even be different between two persons with the same name.

Note the following when we look at the example table:

• The "employee_id" column is the primary key of the "employees" table.
• The "prod_id" column is the primary key of the "orders" table.
• The "employee_id" column in the "orders" table is used to check the persons in the "employees" table, and their names are not used.

------------------------------------------

Employees:

Employee_id	Name
01	Hansen, OLA
02	Svendson, Tove
03	Svendson, Stephen
04	Pettersen, Kari

Orders:

Prod_id	Product	Employee_id
234	Printer	01
657	Table	03
865	Chair	03

------------------------------------------

View two tables

We can view data from two tables, as shown in:

Example

Who placed the order and what did they order?

SELECT Employees.Name, Orders.Product FROM Employees, Orders WHERE Employees.Employee_ID=Orders.Employee_ID

Result

Name	Product
Hansen, OLA	Printer
Svendson, Stephen	Table
Svendson, Stephen	Chair

Example

Who ordered the printer?

SELECT Employees.Name FROM Employees, Orders WHERE Employees.Employee_ID=Orders.Employee_ID AND Orders.Product='Printer'

Result

Name
Hansen, OLA

------------------------------------------

Use joins

Or we can use join to select two tables, as shown in:

Internal Connection example

Syntax

SELECT field1, field2, field3 FROM first_table INNER JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield

Who placed the order and what did they order?

SELECT Employees.Name, Orders.Product FROM Employees INNER JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID

Inner join returns all record rows matching the two tables. If there are records that do not match the orders table in the Employees table, these records will not be listed.

Name	Product
Hansen, OLA	Printer
Svendson, Stephen	Table
Svendson, Stephen	Chair

Left join example

Syntax

SELECT field1, field2, field3 FROM first_table LEFT JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield

List all employees and their orders

SELECT Employees.Name, Orders.Product FROM Employees LEFT JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID

Left join returns all record rows from the first table, even if there are records that do not match the second table (orders. If the Employees table has record rows that do not match the orders table, they will also be listed.

Result

Name	Product
Hansen, OLA	Printer
Svendson, Tove
Svendson, Stephen	Table
Svendson, Stephen	Chair
Pettersen, Kari

Right join example

Syntax

SELECT field1, field2, field3 FROM first_table RIGHT JOIN second_table ON first_table.keyfield = second_table.foreign_keyfield

List all orders and their orders.

SELECT Employees.Name, Orders.Product FROM Employees RIGHT JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID

Right join returns all record rows from two tables, even if there are records that do not match the first table (employees. If the orders table has record rows that do not match the Employees table, they will also be listed.

Result

Name	Product
Hansen, OLA	Printer
Svendson, Stephen	Table
Svendson, Stephen	Chair

Example

Who ordered the printer?

SELECT Employees.Name FROM Employees INNER JOIN Orders ON Employees.Employee_ID=Orders.Employee_ID WHERE Orders.Product = 'Printer'

Result

Name
Hansen, OLA