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.
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
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;
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;
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.