In this lesson our instructor talks about SQL: Joins. First, he discusses DATETIME data type, modeling orders, customers table, and joins. Then he talks about join conditions, implicit joins, aliases, and three required homework problems.
MySQL provides the DATETIME data type for storing a timestamp.
The PHP date() function can be used to generate properly formatted DATETIME strings:
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
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
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
The join condition specified after the ON keyword functions the same way that a where condition
An INNER JOIN can also be implicitly defined by using a WHERE clause in the following
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
Lecture Slides are screen-captured images of important points in the lecture. Students can download and print out these lecture slide images to do practice problems as well as take notes while watching the lecture.