Joins in SQL

One of the most powerful features of SQL is its capability to gather and manipulate data from across several tables. Without this feature we would have to store all the data elements necessary for each application in one table. Without common tables we would need to store the same data in several tables. Imagine having to redesign, rebuild, and repopulate our tables and databases every time our user needed a query with a new piece of information. The JOIN statement of SQL enables us to design smaller, more specific tables that are easier to maintain than larger tables.

This information will enable us to gather and manipulate data across several tables. We will understand and be able to do the following:

• Perform an outer join

• Perform a left join

• Perform a right join

• Performan equi-join

• Perform a non-equi-join

• Join a table to itself

Multiple Tables in a Single SELECT Statement #

The SELECT Statement, when we learned about SELECT and FROM.

NOTE: The queries in today’s examples were produced using Borland’s ISQL tool. We will notice some differences between these queries and the ones that we used earlier in the book. For example, these queries do not begin with an SQL prompt. Another difference is that ISQL does not require a semicolon at the end of the statement. (The semicolon is optional in ISQL.) But the SQL basics are still the same.

SQL> SELECT * FROM EMP

To join two tables, type this:

Select * from emp,dept

Finding the Correct Column #

When we joined EMP and DEPT, we used SELECT *, which returned all the columns in both tables. In joining ORDERS to PART, the SELECT statement is a bit more complicated:

SQL> SELECT O.ORDEREDON, O.NAME, O.PARTNUM, P.PARTNUM, P.DESCRIPTION

SQL is smart enough to know that ORDEREDON and NAME exist only in ORDERS and that DESCRIPTION exists only in PART, but what about PARTNUM, which exists in both? If we have a column that has the same name in two tables, we must use an alias in our SELECT clause to specify which column we want to display.

A common technique is to assign a single character to each table, as we did in the FROM clause:

FROM ORDERS O, PART P

We use that character with each column name, as we did in the preceding SELECT clause. The SELECT clause could also be written like this:

SQL> SELECT ORDEREDON, NAME, O.PARTNUM, P.PARTNUM, DESCRIPTION

But remember, someday we might have to come back and maintain this query. It doesn’t hurt to make it more readable. Now back to the missing statement.

Non-Equi Joins #

Because SQL supports an equi-join, we might assume that SQL also has a non-equi-join. Whereas the equi- join uses an (=) sign in the WHERE statement, the non-equi-join uses everything but an > or < sign. For example:

SQL> SELECT O.NAME, O.PARTNUM, P.PARTNUM, O.QUANTITY * P.PRICE TOTAL FROM
ORDERS O, PART P WHERE O.PARTNUM > P.PARTNUM;

Outer Joins versus Inner Joins #

Just as the non-equi-join balances the equi-join, an outer join complements the inner join. An inner join is where the rows of the tables are combined with each other, producing a number of new rows equal to the product of the number of rows in each table. Also, the inner join uses these rows to determine the result of the WHERE clause. An outer join groups the two tables in a slightly different way. Using the PART and ORDERS tables from the previous examples, perform the following inner join:

SQL> SELECT P.PARTNUM, P.DESCRIPTION, P.PRICE, O.NAME, O.PARTNUM FROM PART P
JOIN ORDERS O ON O.PARTNUM = 54;

Note: The syntax we used to get this join JOIN ON is not ANSI standard. The implementation we used for this example has additional syntax. We are using it here to specify an inner and an outer join. Most implementations of SQL have similar extensions. Notice the absence of the WHERE clause in this type of join.

Set Operators #

• Set operators combine the results of two component queries into a single result.
• Queries containing set operators are called compound queries.
• They combine the results of two or more select statements into one result.
• A Query may therefore consists of two or more SQL statements linked by one or more set operators.
• Set operators are often called vertical joins,because the join is not according to rows between two tables,but columns.

• You can combine multiple queries using the set operators UNION, UNION ALL, INTERSECT, and MINUS.
• All set operators have equal precedence. If a SQL statement contains multiple set operators, Oracle evaluates them from the left to right if no parentheses explicitly specify another order.
• The corresponding expressions in the select lists of the component queries of a compound query must match in number and datatype.

If component queries select character data, the datatype of the return values are determined as follows:

• If both queries select values of data type CHAR, the returned values have data type CHAR.

• If either or both of the queries select values of datatype VARCHAR2, the returned values have datatype VARCHAR2.

Restrictions on set operators #

• The set operators are not valid on columns of type BLOB, CLOB, BFILE, varray, or nested table.

• The UNION, INTERSECT, and MINUS operators are not valid on LONG columns.

• To reference a column, you must use an alias to name the column.

• You cannot also specify the for_update_clause with these set operators.

UNION #

All rows selected by either query.

SQL> select job from emp where dept no=10
union
select job from emp where dept no=30;

UNION ALL #

All rows selected by either query, including all duplicates.

SQL> select job from emp where dept no=10
union all
select job from emp where dept no=30;

INTERSECT #

All distinct rows selected by both queries.

SQL> select job from emp where dept no=10
intersect
select job from emp where dept no=30;

MINUS #

All distinct rows selected by the first query but not the second.

SQL> select job from emp where dept no=10
minus
select job from emp where dept no=30;