Oracle: Use only few tables in WHERE clause but mentioned more tables in 'FROM' in a jon S-CodePudding

What will happen in an Oracle SQL join if I don't use all the tables in the WHERE clause that were mentioned in the FROM clause?

Example:

SELECT A.*  
FROM A, B, C, D
WHERE A.col1 = B.col1;

Here I didn't use the C and D tables in the WHERE clause, even though I mentioned them in FROM. Is this OK? Are there any adverse performance issues?

CodePudding user response：

It is poor practice to use that syntax at all. The FROM A,B,C,D syntax has been obsolete since 1992... more than 30 YEARS now. There's no excuse anymore. Instead, every join should always use the JOIN keyword, and specify any join conditions in the ON clause. The better way to write the query looks like this:

SELECT A.*  
FROM A
INNER JOIN B ON A.col1 = B.col1
CROSS JOIN C
CROSS JOIN D;

Now we can also see what happens in the question. The query will still run if you fail to specify any conditions for certain tables, but it has the effect of using a CROSS JOIN: the results will include every possible combination of rows from every included relation (where the "A,B" part counts as one relation). If each of the three parts of those joins (A&B, C, D) have just 100 rows, the result set will have 1,000,000 rows (100 * 100 * 100). This is rarely going to give the results you expect or intend, and it's especially suspect when the SELECT clause isn't looking at any of the fields from the uncorrelated tables.

CodePudding user response：

Any table lacking join definition will result in a Cartesian product - every row in the intermediate rowset before the join will match every row in the target table. So if you have 10,000 rows and it joins without any join predicate to a table of 10,000 rows, you will get 100,000,000 rows as a result. There are only a few rare circumstances where this is what you want. At very large volumes it can cause havoc for the database, and DBAs are likely to lock your account.

If you don't want to use a table, exclude it entirely from your SQL. If you can't for reason due to some constraint we don't know about, then include the proper join predicates to every table in your WHERE clause and simply don't list any of their columns in your SELECT clause. If there's a cost to the join and you don't need anything from it and again for some very strange reason can't leave the table out completely from your SQL (this does occasionally happen in reusable code), then you can disable the joins by making the predicates always false. Remember to use outer joins if you do this.

Native Oracle method:

   WITH data AS (SELECT ROWNUM col FROM dual CONNECT BY LEVEL < 10) -- test data
   SELECT A.*  
    FROM  data a,
          data b,
          data c,
          data d
    WHERE a.col = b.col
      AND DECODE('Y','Y',NULL,a.col) = c.col( )
      AND DECODE('Y','Y',NULL,a.col) = d.col( )

ANSI style:

   WITH data AS (SELECT ROWNUM col FROM dual CONNECT BY LEVEL < 10)
   SELECT A.*  
    FROM  data a
          INNER JOIN data b ON a.col = b.col
          LEFT OUTER JOIN data c ON DECODE('Y','Y',NULL,a.col) = b.col
          LEFT OUTER JOIN data d ON DECODE('Y','Y',NULL,a.col) = d.col

You can plug in a variable for the first Y that you set to Y or N (e.g. var_disable_join). This will bypass the join and avoid both the associated performance penalty and the Cartesian product effect. But again, I want to reiterate, this is an advanced hack and is probably NOT what you need. Simply leaving out the unwanted tables it the right approach 95% of the time.