Author: Moulton Kendall

In the previous article I discussed single table SQL queries. In this article I am taking you one level advance to SQL multi-table queries.

I am using USER, GAME and USER_GAME tables from my previous article SQL in Action I - Single Table Queries

SQL - JOIN:

SQL JOIN is performed in the WHERE clause of SELECT query. JOIN allows SQL primary key foreign key operations. If an attribute is ambiguous then you must specify to which table of the database it belongs.

Always use aliases for database tables. It saves time.

SQL - 2 Tables JOIN:

Let us apply join on two database tables and get names of all those games which were purchased on 30-12-2006 or 03-09-2007 .

SELECT DISTINCT gameName
FROM GAME AS g, USER_GAME AS ug
WHERE
g.gameID=ug.gameID AND
(purchaseDate=" 30-12-2006 " OR purchaseDate=" 03-09-2007 ");

Remember to add DISTINCT in order to avoid repetition in the database query results.

Enclose query conditions in brackets to get accurate results. 

SQL - JOIN on more than 2 tables:

In order to get user names who bought Shooter X game we have to use a SQL query that apply JOIN on three tables.

SELECT DISTINCT u.id,fname, lname
FROM USERS AS u, GAME AS g, USER_GAME AS ug
WHERE
u.id=ug.id AND
g.gameID=ug.gameID AND
g.gameName="Shooter X"

SQL - SELF-JOIN:

Self JOIN is applied on the same table of the database. One should be very much careful that self-join on any table does not produce redundancy.

An example of self-join is to list all pairs of users who belong to the same country.

SELECT u1.fname,u2.fname
FROM USERS u1, USERS u2
WHERE u1.country=u2.country AND u1.fname<u2.fname

Notice that we are not using u1.fname=u2.fname OR u1.fname<>u2.fname because it will cause redundancy. The output of the query will be

SQL - UNION:

In SQL, UNION and OR are logically related but the results may differ. UNION retrieves the results of both queries and the result set contains records of both SQL queries from the database.

(SELECT id FROM USERS WHERE country=” USA ”)
UNION
(SELECT id FROM USER_GAME WHERE date=” 14-08-2007 ”);

SQL - INERSECT:

In SQL, INTERSECT and AND are logically related but the results may differ. INTERSECT retrieves those records from the database which are similar in sub-query 1 and sub-query 2.

(SELECT id
FROM USERS
WHERE country=” UK ”)
INTERSECT
(SELECT id
FROM USER_GAME
WHERE gameID=1)

SQL - DIFFERENCE: 

In SQL, DIFFERENCE and NOT are logically related. Difference minuses the records of second sub-query from the first sub-query and those database records from result set are returned which are in result set of sub-query 1 but not in result set of sub-query 2.

(SELECT gameID FROM GAME)
MINUS
(SELECT gameID FROM USER_GAME WHERE id<>3);

SQL - NESTED QUERY:

In SQL, a nested query is equivalent to JOIN. The nested query runs with IN, NOT IN, EXISTS and NOT EXISTS.

Consider our previous JOIN example

SELECT DISTINCT gameName
FROM GAME AS g, USER_GAME AS ug
WHERE g.gameID=ug.gameID AND
(purchaseDate=" 30-12-2006 " OR purchaseDate=" 03-09-2007 ");

The SQL nested query of above JOIN query will be as below

SQL - IN:

SELECT gameNAME
FROM GAME
WHERE gameID IN
       SELECT DISTINCT gameID
       FROM USER_GAME
       WHERE (purchaseDate=" 30-12-2006 " OR purchaseDate=" 03-09-2007 ");

In SQL nested queries with IN, first the inner query is executed, result is retrieved from the database table and the result is sent to the outer query. The outer query make use of these values for IN and is executed.

For example the inner query returns (value1, value2, value3 ….) from the database. Now the outer query will execute with these values like this WHERE gameID IN (value1, value2, value3 ….) and returns records from the database.

SQL - EXISTS:

SQL EXISTS returns boolean true or false values.

SELECT gameNAME
FROM GAME
AND EXISTS
      SELECT gameID
      FROM USER_GAME
      WHERE (purchaseDate=" 30-12-2006 " OR purchaseDate=" 03-09-2007 ");

First the outer query is executed which returns a number of records from the database. For each record the inner query is executed and if result is true then that record is in final result. For NOT EXISTS if result is false then that record will be in final result of the database.

 More Oracle Articles, Database Articles and DBA Tips
   Database Security: Step by step guideline
   Great Tips for Removing Jobs from Job Queue Automatically!!
   DBA Tips for Finding Oracle Log Files in Oracle R12!!
   Exceptional Tips for Exceptions in Oracle
   Easy, Quick and Safe Oracle Patching Secrets!!