AS enables users to rename columns or table selections with an alias.
AS can also be applied to aggregate columns.
For future reference, the relational schema used in this course is included below.
JOINs allow users to relate data in multiple tables together. There are several kinds of joins, including
OUTER JOIN, and self-join.
JOINs typically operate by connecting the primary key of one table to the foreign key of another table. Typical syntax follows, where A and B are names of tables, pka is the primary key of table A, and fka is the foreign key of table B that connects it to A.
INNER JOINs work by checking the corresponding columns of both tables for matching values. If it finds one, then it connects the corresponding rows of those two tables into a single table.
INNER JOINs are actually the default
JOIN, so most SQL engines will allow you to specify
JOIN only and still get the
INNER JOIN functionality.
As shown below, table names are frequently aliased, and the
AS statement can be omitted for brevity.
Types of JOINs
The various types of joins come into play when the tables being
JOINed have missing values. The various types of
JOINs allow the user to rectify this situation in various ways.
JOINs, the left table is the table that follows the
FROM statement. The right table is the table that follows
Examples of Join Types
The following example is presented in the Udemy course and reproduced here. Two columns each of two tables, A and B, are represented.
In the following examples,
LEFT JOINs can be turned into
RIGHT JOINs by either replacing the relevant word in the queries. It is also possible to accomplish the same thing by swapping which tables appears after
LEFT OUTER JOIN
FULL OUTER JOIN
LEFT OUTER JOIN with WHERE
FULL OUTER JOIN with WHERE
UNION combines the results of multiple
SELECT statements into a single set of results. Two rules must be followed when using
- All queries must return the same number of columns,
- Corresponding columns in the queries must have compatible data types.
The syntax follows.
Reasons to use
UNIONremoves all duplicate rows unless
UNION ALLis used. Duplicate rows are defined as having the same data across all columns.
UNIONis frequently used to combine data from similar tables that are not perfectly normalized, meaning, the information has not been combined into a single table yet.