Educator

SQL: Joins

• MySQL provides the DATETIME data type for storing a timestamp.
• The PHP date() function can be used to generate properly formatted DATETIME strings: ‘YYYY-MM-DD HH:MM:SS’.
• It is advantageous to store timestamps as DATETIME data rather than as simple strings, like VARCHAR, because MySQL can perform date calculations on DATETIME columns to restrict the result sets of queries.
• A join enables us to extract data from more than one table in a single SELECT query.
• The basic type of join is called an inner join and is specified by using the INNER JOIN SQL keywords:

  SELECT * FROM orders INNER JOIN customers;

• An inner join forms the Cartesian product of all of the rows from all tables specified, meaning that the result set will contain a row for each combination of the rows from one table with all of the rows from the other tables.
• A join condition can be provided using the ON keyword in order to restrict the rows present in an inner join result set based on specified criteria. For example:

  SELECT * FROM orders INNER JOIN customers ON orders.custID=customers.custID;

• Join queries involve multiple tables so to specify a column from one of the tables in a join, you use the syntax:

  tableName.columnName

• The join condition specified after the ON keyword functions the same way that a where condition does.
• An INNER JOIN can also be implicitly defined by using a WHERE clause in the following manner:

  SELECT * FROM orders, customers WHERE orders.custID=customers.custID;

• In queries using joins, it is often useful to define aliases, which is a shortened way to refer to a table in a query. In SQL, aliases are defined using the AS keyword:

  SELECT o.orderID, c.firstName, c.lastName
  FROM orders AS o
  INNER JOIN customers AS c
  ON o.custID=c.custID;

• Additional Resources:
  • SQL Command Reference - JOIN Syntax