Using Joins

Each row of the left table is connected to each row of the right table.

No logical condition is required with the CROSS JOIN clause.

Joins may be specified with two different syntaxes: implicit and explicit. Although you will probably encounter implicit joins, the explicit syntax allows for more control and is considered more correct. While a few implicit examples will be shown, you should focus on the explicit syntax.

Each row of a table is connected to each row of the other table.

The result set contains 10 * 20 = 200 rows.

12
SELECT *
    FROM Owner, Car;

Each row of a table is connected to each row of the other table.

The result set contains 10 * 20 = 200 rows.

12
SELECT *
    FROM Owner CROSS JOIN Car;

A WHERE clause may be specified to reduce the rows returned from a join.

123
SELECT *
    FROM Owner, Car
WHERE HP > 250;

123
SELECT *
    FROM Owner CROSS JOIN Car
WHERE HP > 250;

Cross joining multiple tables quickly increases the number of result rows.

The result set contains 10 * 20 * 3 = 600 rows.

12
SELECT *
    FROM Owner, Car, Stolen;

12
SELECT *
    FROM Owner CROSS JOIN Car CROSS JOIN Stolen;

You can also cross join a table with itself.

You can qualify the column name by adding the table name:

12
SELECT Official.Name
    FROM Official;

Having to repeat the full table name as a qualifier each time can be cumbersome. This is why you can use table aliases to introduce abbreviated table names and use the abbreviated form as a qualifier in the same SELECT statement.

You can use table aliases in the projection list. Table aliases are defined in the FROM clause.

12
SELECT o.Name, o.PNr
    FROM Official o;

You can use the (optional) keyword AS in the definition of a table aliases:

12
SELECT o.Name, o.PNr
    FROM Official AS o;

If a table alias is defined in the FROM clause, you are not allowed to use the corresponding table name for qualification of a column name:

One row of the left table and one row of the right table are always joined to a common result row, provided that the JOIN condition is fulfilled.

JOIN Condition: L.X = R.Y

One row of a table and one row of another table are always connected to a common result row provided that the JOIN condition is fulfilled.

The implicit JOIN condition is part of the WHERE clause.

The explicit JOIN condition is independent of the WHERE clause.

To whom is each car registered?

123
SELECT *
    FROM Owner, Car
WHERE OwnerID = Owner; 

12
SELECT *
    FROM Owner JOIN Car ON OwnerID = Owner;

Both JOIN and INNER JOIN are acceptable syntax variants.

The INNER JOIN is the most important JOIN variant (and therefore the default).

To whom is each car registered?

12
SELECT *
  FROM Owner INNER JOIN Car ON OwnerID = Owner;

You can specify columns in the projection list of the JOIN.

To whom is each car registered?

12
SELECT Name, Brand, Color
    FROM Owner JOIN Car ON OwnerID = Owner;

You can use table aliases in the projection list of the JOIN. The JOIN condition can also refer to table aliases. If column names are not unique, you must qualify them with the table aliases.

To whom is each car registered?

12
SELECT o.Name, c.Brand, c.Color
    FROM Owner o JOIN Car c ON o.OwnerID = c.Owner;

You do not need to include a column from every involved table in the projection list. In addition to the JOIN condition you can specify a WHERE clause.

Who has a black car registered (at least one)?

123
SELECT DISTINCT o.Name 
    FROM Owner o JOIN Car c ON o.OwnerID = c.Owner
WHERE c.Color = 'black';

You can build the JOIN condition on multiple columns.

Who within the EU has a black car registered (at least one)?

12345
SELECT DISTINCT o.Name AS "Owner's name"
    FROM Owner_EU o JOIN Car_EU c ON o.Country = c.Country 
                                  AND o.OwnerID = c.Owner
WHERE c.Color = 'black'
ORDER BY o.Name; 

You can join a table to itself. So-called self joins will require table aliases to be used.

Who is the manager of each employee?

12
SELECT e.Name AS Employee, m.Name AS Manager
FROM Official e JOIN Official m ON e.Manager = m.PNr;

Combining more than two tables will require additional join conditions. To avoid cross joins, you must supply a valid join condition for each pairwise combination of tables in the join.

To whom is each stolen car registered?

123
SELECT o.Name, c.Brand, c.Color, c.PlateNumber
FROM Owner o JOIN Car c ON o.OwnerID = c.Owner
             JOIN Stolen s ON c.PlateNumber = s.PlateNumber;

You can use different comparison operators other than equality in the JOIN condition.

Which other owners is a given owner older than? (Note that this is not a very meaningful question.)

12
SELECT o.Name AS "older", y.Name AS "younger"
    FROM Owner o JOIN Owner y ON o.Birthday < y.Birthday;

You can use calculations and functions in the JOIN condition.

Which owners were born in the same year as other owner(s)?

123
SELECT o1.Name, o1.Birthday, o2.Name, o2.Birthday
    FROM Owner o1 JOIN Owner o2 ON YEAR(o1.Birthday) = YEAR(o2.Birthday)
                                AND o1.OwnerID < o2.OwnerID;

Left Outer Joins:

• One row of a table and one row of another table are always connected to a common result row - provided the JOIN condition is fulfilled.
• In addition, rows of the left table without matching row in the right table are copied to the query result. The missing values ​​(from the right table) are filled with NULL values.

Which car is registered to which individual or company? Individuals and companies that currently do not have any cars registered should also be included. Cars without a registration should not be included in the result.

12
SELECT Name, CarID, Brand
    FROM Owner LEFT OUTER JOIN Car ON OwnerID = Owner;

Right Outer Joins:

• One row of a table and one row of another table are always connected to a common result row, provided that the JOIN condition is fulfilled.
• In addition, rows of the right table without matching row in the left table are copied to the query result. The missing values ​​(from the left table) are filled with NULL values.
• Note that reversing the order of tables in the join condition AND switching the direction of the join ( LEFT ↔ RIGHT) produces the same result set.

Which car is registered to which individual or company? Cars without a current registration should also be included. Individuals and companies that currently do not have a car registered should not be included in the result.

12
SELECT Name, CarID, Brand
    FROM Owner RIGHT OUTER JOIN Car ON OwnerID = Owner;

One row of a table and one row of another table are always connected to a common result row, provided that the JOIN condition is fulfilled. In addition, rows of both tables without matching records are copied to the query result. The missing values ​​(from the other table) are filled with NULL values.

Which car is registered to which individual or company? Individuals and companies that currently do not have a car registered should be included. Cars without a current registration should be included in the result.

12
SELECT Name, CarID, Brand
    FROM Owner FULL OUTER JOIN Car ON OwnerID = Owner;

Each type of outer join supports joining a table with itself. The following shows an example of a left outer join operating on a single table.

• As with inner self joins, table aliases must be used.
• Who is the manager of which employee?
• Employees without a manager should be included.
• Only managers with employees should be included.

12
SELECT e.Name AS Employee, m.Name AS Manager
    FROM Official e LEFT OUTER JOIN Official m ON e.Manager = m.PNr;

Replacing the keyword LEFT with RIGHT or FULL works in the example and leads to different result sets.

• Using RIGHT OUTER JOIN would lead to a result including each official name in the MANAGER column, and only the names of the officials who have a manager assigned in the EMPLOYEE column.

• USING FULL OUTER JOIN would lead to a result including each official name in the MANAGER column, and each official name in the EMPLOYEE column.

Similar to what is supported for cross joins and inner joins, you can also do the following with outer joins:

• Use any comparison operator in the JOIN condition.

• Join more than two tables, and join a table with itself.

• Project the result set to a subset of columns. You do not have to include a column of every table involved in the projection list.

• Rename columns.

• Eliminate duplicates using DISTINCT.

• Order the result set, use aggregate expressions, a GROUP BY clause, or a HAVING clause.

• Use table aliases and reference them in the projection list and join condition. You have to qualify column names with the table names or use aliases if the column names are not unique.

Generally, any join may combine with any of the query operations already seen: column naming, results ordering, aggregates and grouped aggregates, functions and expressions, and so on. Query operations are independent and may be arbitrarily combined to produce as simple or complex a result as desired.