What type of join is needed when you wish to return rows when there is at least one match in both tables quizlet?

Recommended textbook solutions

Introduction to Algorithms

3rd EditionCharles E. Leiserson, Clifford Stein, Ronald L. Rivest, Thomas H. Cormen

720 solutions

Engineering Electromagnetics

8th EditionJohn Buck, William Hayt

483 solutions

Computer Organization and Design MIPS Edition: The Hardware/Software Interface

5th EditionDavid A. Patterson, John L. Hennessy

220 solutions

Introduction to the Theory of Computation

3rd EditionMichael Sipser

389 solutions

INNER JOIN (a.k.a. "simple join"): Returns all rows for which there is at least one match in BOTH tables. This is the default type of join if no specific JOIN type is specified.

LEFT JOIN (or LEFT OUTER JOIN): Returns all rows from the left table, and the matched rows from the right table; i.e., the results will contain all records from the left table, even if the JOIN condition doesn't find any matching records in the right table. This means that if the ON clause doesn't match any records in the right table, the JOIN will still return a row in the result for that record in the left table, but with NULL in each column from the right table.

RIGHT JOIN (or RIGHT OUTER JOIN): Returns all rows from the right table, and the matched rows from the left table. This is the exact opposite of a LEFT JOIN; i.e., the results will contain all records from the right table, even if the JOIN condition doesn't find any matching records in the left table. This means that if the ON clause doesn't match any records in the left table, the JOIN will still return a row in the result for that record in the right table, but with NULL in each column from the left table.

FULL JOIN (or FULL OUTER JOIN): Returns all rows for which there is a match in EITHER of the tables. Conceptually, a FULL JOIN combines the effect of applying both a LEFT JOIN and a RIGHT JOIN; i.e., its result set is equivalent to performing a UNION of the results of left and right outer queries.

CROSS JOIN: Returns all records where each row from the first table is combined with each row from the second table (i.e., returns the Cartesian product of the sets of rows from the joined tables). Note that a CROSS JOIN can either be specified using the CROSS JOIN syntax ("explicit join notation") or (b) listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria ("implicit join notation").

The following queries produce the same results.

select customer_name, customer_city
from customer, salesman
where customer.salesman_id = salesman.salesman_id
and salesman.lname = 'SMITH';

select customer_name, customer_city
from customer
where customer.salesman_id =
(select salesman_id
from salesman
where lname = 'SMITH');

(or Left Outer Join)

Returns all rows from the left table, and the matched rows from the right table;

i.e., the results will contain all records from the left table, even if the JOIN condition doesn't find any matching records in the right table.

This means that if the ON clause doesn't match any records in the right table, the JOIN will still return a row in the result for that record in the left table, but with NULL in each column from the right table.

Ex: Produces a complete set of recors from Table A, with the matching records (where available) in Table B. If no match, right side will contain null.