3.5 Equijoins, self-joins, and cross-joins

Question 1

What is the result of the following equijoin query?

SELECT Class.Name, Student.Name
FROM Class
INNER JOIN Student
ON Student.Code = Class.Code;

Answer 1

The INNER JOIN returns rows where Student.Code matches Class.Code.

Question 2

What is the result of the following equijoin query?

SELECT Name, Address
FROM Buyer
LEFT JOIN Property
ON Price < MaxPrice;

Answer 2

Question 3

Which join type is the following query?

Answer 3

Self-join

SELECT A.Name, B.Name
FROM EmployeeManager A
INNER JOIN EmployeeManager B
ON B.ID = A.Manager;

Question 4

What does the following cross-join query result in?

SELECT Model, Gigabytes
FROM IPhone
CROSS JOIN Storage;

Answer 4

CROSS JOIN generates all combinations of IPhone models and storage capacities.

Question 5

What is the result of the following query?

SELECT Student.Name
FROM Class
INNER JOIN Student
ON StudentGrade > AverageGrade
AND Student.Code = Class.Code;

Answer 5

Question 6

What is the primary use of equijoins?

Answer 6

To combine rows from tables where specified columns equal each other. Equijoins are typical for matching columns using the = operator.

Question 7

Which SQL command combines two tables using conditions other than the equality operator?

Answer 7

Non-equijoin.

Non-equijoins utilize comparison operators other than =.

Question 8

What does it mean when we refer to a ‘Cartesian product’ in SQL?

Answer 8

The result of a cross-join containing all possible row combinations. A Cartesian product pairs each row from the first table with every row from the second.

Question 9

How do we distinguish same-table columns in a self-join?

Answer 9

Using aliases. Aliases act as prefixes to differentiate joined columns of the same table.

Question 10

What is the result when using non-equijoin operators such as < and >?

Answer 10

Returns rows where the compare columns meet the specified non-equal condition.

Unlike equijoins, non-equijoins use operators like < or > instead of =.

Question 11

Why are aliases necessary in self-joins?

Answer 11

To distinguish columns from the same tables. Aliases are used as prefixes to differentiate between the same table joined on itself.

Question 12

Identify the join clause(s) usable in non-equijoin queries.

Answer 12

All JOIN clauses.

Non-equijoin conditions can apply with INNER, LEFT, RIGHT, or FULL JOINs.

Question 13

What does a cross-join do without comparing columns?

Answer 13

Combines all rows from two tables into all possible pairs.

CROSS JOIN results in the Cartesian product of two tables.