Zend PHP 7 Certification – Databases – Joins

22nd January 2018

This post covers the Joins section of the Databases chapter when studying for the Zend PHP 7 Certification.

An SQL JOIN clause is used to combine records from two or more tables in a database. A JOIN is a means for combining fields from two tables by using values common to each.

The main types of joins used are Inner Join, Left Join and Right Join.

INNER JOIN

The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are rows in table 1 that do not have matches in table 2, then the rows in table 1 will NOT be listed.

Note that INNER JOIN is the same is just using JOIN.

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

Zend PHP 7 Certification

LEFT JOIN

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

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;

Zend PHP 7 Certification

RIGHT JOIN

The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match.

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;

Zend PHP 7 Certification

Real life examples of joins could include a business that has two tables in its database: Customers and Orders. We might want to ask which customers have an order which is still pending. To do this, we would run a select statement containing a JOIN clause.

SELECT c.customer_id, c.first_name, c.last_name, o.order_status 
FROM Customers c
INNER JOIN Orders o
ON c.customer_id = o.customer_id
WHERE o.order_status = "pending"

Note: This article is based on PHP version 7.1.