| 1: | A Cartesian product
represents all possible combinations of rows in two or more tables represents the combinations of matching rows from two or more tables represents the rows from one table matched with rows from a second table; where this is not possible, the result row is filled with NULLs where the data from the second table would have been none of the above
|
| 2: | A left join
represents all possible combinations of rows in two or more tables represents the combinations of matching rows from two or more tables represents the rows from one table matched with rows from a second table; where this is not possible, the result row is filled with NULLs where the data from the second table would have been none of the above
|
| 3: | An equijoin:
represents all possible combinations of rows in two or more tables represents the combinations of matching rows from two or more tables represents the rows from one table matched with rows from a second table; where this is not possible, the result row is filled with NULLs where the data from the second table would have been none of the above
|
| 4: | A correlated subquery is called this because
it correlates rows between tables it correlates rows in a single table it correlates two joins it correlates the rows in an outer query with rows in an inner query
|
| 5: | The difference between the two queries marked 5.1 and 5.2 in the following text is that
there is no difference they return different data they return the same data but the left join (Query 5.1) is likely to execute faster they return the same data but the subquery (Query 5.2) is likely to execute faster
Query 5.1:
select employee.name
from employee left join assignment
on employee.employeeID = assignment.employeeID
where clientID is null;
Query 5.2:
select e.name, e.employeeID
from employee e
where not exists
(select *
from assignment
where employeeID = e.employeeID);
|
