View the Exhibit and examine the structure of the DEPARTMENTS and LOCATIONS tables. You want to display all the cities and the corresponding departments in them, if any. Which query would give you the required output?
A. SELECT location_id LOC, city, department_id DEPT
FROM locations LEFT OUTER JOIN departments
USING (location_id);
B. SELECT location_id LOC, city, department_id DEPT
FROM locations RIGHT OUTER JOIN departments
USING (location_id);
C. SELECT l.location_id LOC, l.city, d.department_id DEPT
FROM locations l LEFT OUTER JOIN departments d
USING (location_id);
D. SELECT l.location_id LOC, l.city, d.department_id DEPT
FROM locations l FULL OUTER JOIN departments d
USING (location_id);
Exhibit:
DEPARTMENTS table:
LOCATIONS table:
Theory:
Since the question or rather answers contain only SELECT statements with different types of JOIN, hence the information required to answer the question can be found in the documentation on
SELECT and
JOINS. The syntax of JOIN queries within SELECT statement is as follows:
An
INNER JOIN or
JOIN (sometimes called a simple join) is a join of two or more tables that returns only those rows that satisfy the join condition. Use the
ON clause to specify a join condition. Doing so lets you specify join conditions separate from any search or filter conditions in the WHERE clause. When you are specifying an equijoin of columns that have the same name in both tables, the
USING (columns) clause indicates the columns to be used. You can use this clause only if the join columns in both tables have the same name. Within this clause, do not qualify the column name with a table name or table alias.
OUTER JOINS return all rows that satisfy the join condition and also returns some or all of those rows from one table for which no rows from the other satisfy the join condition. You can specify two types of outer joins: a
conventional outer join using the table reference syntax on both sides of the join, or a
partitioned outer join using the query partition clause on one side or the other. A partitioned outer join is similar to a conventional outer join except that the join takes place between the outer table and each partition of the inner table. This type of join lets you selectively make sparse data more dense along the dimensions of interest. This process is called data densification. You can specify the optional
OUTER keyword following
RIGHT,
LEFT, or
FULL to explicitly clarify that an outer join is being performed. To write a query that performs an outer join of tables A and B and returns all rows from A (a left outer join), use the
LEFT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of B in the join condition in the WHERE clause. For all rows in A that have no matching rows in B, Oracle Database returns null for any select list expressions containing columns of B. To write a query that performs an outer join of tables A and B and returns all rows from B (a right outer join), use the
RIGHT [OUTER] JOIN syntax in the FROM clause, or apply the outer join operator (+) to all columns of A in the join condition in the WHERE clause. For all rows in B that have no matching rows in A, Oracle returns null for any select list expressions containing columns of A. To write a query that performs an outer join and returns all rows from A and B, extended with nulls if they do not satisfy the join condition (a full outer join), use the
FULL [OUTER] JOIN syntax in the FROM clause. You cannot compare a column with a subquery in the WHERE clause of any outer join, regardless which form you specify.
Answer:
A - Correct
B - Wrong - the query would display all departments and cities if any in that departments instead of displaying all cities and departments if any
C - Wrong - cannot use table aliases when joining with USING
D - Wrong - this query would display all cities and all departments event those that don't have a city
0 comments: