This post covers the Joins section of the Databases chapter when studying for the Zend PHP 7 Certification.
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 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.
INNER JOIN is the same is just using
SELECT Customers.CustomerName, Orders.OrderID FROM Customers INNER JOIN Orders ON Customers.CustomerID=Orders.CustomerID
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;
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;
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
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.