Performing Joins Using the SQL92 Syntax

Performing Joins Using the SQL/92 Syntax

The join syntax you ve seen so far used Oracle s syntax for joins. Oracle s syntax is based on the ANSI SQL/86 standard. With the introduction of Oracle9 i , the database also implements the ANSI SQL/92 standard syntax for joins. To make your SQL fully compliant with the new standard, you should use SQL/92 in your queries. In addition, you ll see how using SQL/92 helps you avoid unwanted Cartesian products.

Note  

You can find out more about the ANSI SQL standards at their web site www.ansi.org and perform a search on SQL.

Performing Inner Joins on Two Tables Using SQL/92

Earlier, you saw the following query that uses the SQL/86 standard for performing an inner join:

 SELECT p.name, pt.name FROM products p, product_types pt WHERE p.product_type_id = pt.product_type_id; 

SQL/92 introduced the INNER JOIN and ON clauses for performing an inner join. The following example rewrites the previous query using the INNER JOIN and ON clauses:

 SELECT p.name, pt.name FROM products p INNER JOIN product_types pt ON p.product_type_id = pt.product_type_id; 

You can use non- equijoin operators with the ON clause. Earlier, you saw the following query that uses the SQL/86 standard for performing a non-equijoin:

 SELECT e.first_name, e.last_name, e.title, e.salary,  sg.salary_grade_id FROM employees e, salary_grades sg WHERE e.salary BETWEEN sg.low_salary AND sg.high_salary; 

The following example rewrites this query to use the SQL/92 standard:

 SELECT e.first_name, e.last_name, e.title, e.salary,  sg.salary_grade_id FROM employees e INNER JOIN salary_grades sg ON e.salary BETWEEN sg.low_salary AND sg.high_salary; 

Simplifying Joins with the USING Keyword

SQL/92 allows you to further simplify the join condition through the USING clause, but only when your query has the following limitations:

• Your query must use an equijoin.

• The columns in your equijoin have the same name.

Most of the joins you ll perform will be equijoins , and if you always use the same name as the primary key for your foreign keys, you ll satisfy the previous limitations.

The previous example used an equijoin on the product_type_id columns in the products and product_types tables, so the query may be rewritten to use the USING clause instead of ON . The following example shows this:

 SELECT p.name, pt.name FROM products p INNER JOIN product_types pt USING (product_type_id); 

If you wanted to view the product_type_id , you must only provide this column name on its own without a table name or alias in the SELECT clause. For example:

 SELECT p.name, pt.name, product_type_id FROM products p INNER JOIN product_types pt USING (product_type_id); 

If you tried to provide a table alias with the column, such as p.product_type_id for example, you ll get an error. For example:

 SQL>  SELECT p.name, pt.name, p.product_type_id  2  FROM products p INNER JOIN product_types pt  3  USING (product_type_id);  SELECT p.name, pt.name, p.product_type_id      * ERROR at line 1: ORA-25154: column part of USING clause cannot have qualifier 

Also, you only use the column name on its own within the USING clause. For example, if you try to specify USING (p.product_type_id) in the previous query instead of USING (product _type_id), you get the following error:

 SQL>  SELECT p.name, pt.name, p.product_type_id  2  FROM products p INNER JOIN product_types pt  3  USING (p.product_type_id);  USING (p.product_type_id)    * ERROR at line 3: ORA-01748: only simple column names allowed here 

Caution  

Don t use a table name or alias when referencing columns used in a USING clause. You ll get an error if you do.

Performing Inner Joins on More than Two Tables Using SQL/92

Earlier you saw the following SQL/86 query that retrieves rows from the customers , purchases , products , and product_types tables:

 SELECT c.first_name, c.last_name, p.name AS PRODUCT, pt.name AS TYPE FROM customers c, purchases pr, products p, product_types pt WHERE c.customer_id = pr.customer_id AND p.product_id = pr.product_id AND p.product_type_id = pt.product_type_id; 

The following example rewrites this query using SQL/92; notice how the foreign key relationships are navigated through multiple INNER JOIN and USING clauses:

 SELECT c.first_name, c.last_name, p.name AS PRODUCT, pt.name AS TYPE FROM customers c INNER JOIN purchases pr USING (customer_id) INNER JOIN products p USING (product_id) INNER JOIN product_types pt USING (product_type_id); 

Performing Inner Joins on Multiple Columns Using SQL/92

If your join uses more than one column from the two tables, you provide those columns in your ON clause along with the AND operator. For example, let s say you have two tables named table 1 and table2 and you want to join these tables using columns named column1 and column2 in both tables. Your query would be

 SELECT ... FROM table1 INNER JOIN table2 ON table1.column1 = table2.column1 AND table1.column2 = table2.column2; 

You can further simplify your query though the USING clause, but only if you re performing an equijoin and the column names are identical. For example, the following query rewrites the previous example with the USING clause:

 SELECT ... FROM table1 INNER JOIN table2 USING (column1, column2); 

Performing Outer Joins Using SQL/92

Earlier you saw how to perform outer joins using the outer join operator (+) . SQL/92 uses a different syntax for performing outer joins. Instead of using (+) , you specify the type of join in the FROM clause of your SELECT statement using the following syntax:

 FROM  table1  { LEFT  RIGHT  FULL } OUTER JOIN  table2  

where

• table1 and table2 specify the tables that you want to join.

• LEFT specifies you want to perform a left outer join.

• RIGHT specifies you want to perform a right outer join.

• FULL specifies you want to perform a full outer join; a full outer join uses all rows in table1 and table2 including those that have null values in the columns used in the join. You can t directly perform a full outer join using the (+) operator.

You ll see how to perform left, right, and full outer joins using the SQL/92 syntax in the following sections.

Performing Left Outer Joins Using SQL/92

Earlier you saw the following query that performed a left outer join:

 SELECT p.name, pt.name FROM products p, product_types pt WHERE p.product_type_id = pt.product_type_id (+); 

The next example rewrites this query using the SQL/92 LEFT OUTER JOIN keywords:

 SELECT p.name, pt.name FROM products p LEFT OUTER JOIN product_types pt USING (product_type_id); 

Performing Right Outer Joins Using SQL/92

Earlier you saw the following query that performed a right outer join:

 SELECT p.name, pt.name FROM products p, product_types pt WHERE p.product_type_id (+) = pt.product_type_id; 

The next example rewrites this query using the SQL/92 RIGHT OUTER JOIN keywords:

 SELECT p.name, pt.name FROM products p RIGHT OUTER JOIN product_types pt USING (product_type_id); 

Performing Full Outer Joins Using SQL/92

A full outer join uses all rows in the joined tables including those that have null values in either of the columns used in the join. The following example shows a query that uses the SQL/92 FUL L OUTER JOIN keywords:

  SELECT p.name, pt.name   FROM products p FULL OUTER JOIN product_types pt   USING (product_type_id);  NAME      NAME ------------------------------ ---------- Chemistry     Book Modern Science    Book 2412: The Return    Video Z Files       Video Tank War      Video Supernova     Video From Another Planet   DVD Space Force 9     DVD Creative Yell    CD Pop 3       CD Classical Music    CD My Front Line           Magazine 

Notice that both My Front Line from the products table and Magazine from the product _types table are displayed.

Performing Self Joins Using SQL/92

The following example uses SQL/86 to perform a self join on the employees table:

 SELECT w.last_name  ' works for '  m.last_name FROM employees w, employees m WHERE w.manager_id = m.employee_id; 

The next example rewrites this query to use the SQL/92 INNER JOIN and ON keywords:

 SELECT w.last_name  ' works for '  m.last_name FROM employees w INNER JOIN employees m ON w.manager_id = m.employee_id; 

Performing Cross Joins Using SQL/92

Earlier you saw how omitting a join condition between two tables leads to a Cartesian product. By using the SQL/92 join syntax, you avoid inadvertently producing a Cartesian product because you must always provide an ON or USING clause to join the tables. This is a good thing because you usually don t want a Cartesian product.

If you really want a Cartesian product, the SQL/92 standard requires that you explicitly state this in your query using the CROSS JOIN keywords. In the following example, a Cartesian product between the product_types and products tables is generated using the CROSS JOIN keywords:

 SELECT * FROM product_types CROSS JOIN products;