The WHERE clause can also be a replacement for the JOIN clause. If you know people that have only worked with Oracle databases, they may not even write standard SQL joins. Because of this, you may never see a JOIN statement in an Oracle SQL based script since you’re actually joining tables using WHERE instead.
While joining tables using WHERE may work, I would NOT recommend using this practice frequently unless it’s a vetted standard in a widely known database from a company such as Oracle. After over a decade of SQL use, I’ve still seen strange behavior from these common databases using even the most basic built in commands. This forces a person to rewrite large SQL scripts typically using ANSI standards.
Let’s go back to our last query example:
SELECT
C.FirstName AS FName,
C.LastName AS LName,
I.InvoiceDate,
I.Total,
IL.Quantity,
IL.UnitPrice
FROM CUSTOMER AS C
INNER JOIN INVOICE AS I ON C.CUSTOMERID = I.CUSTOMERID
INNER JOIN INVOICELINE AS IL ON I.INVOICEID = IL.INVOICEID;
If we’re looking at the DBeaver sample database we see that the output returns the first 200 records.
Using the WHERE clause instead, we could write it like this:
SELECT
C.FirstName AS FName,
C.LastName AS LName,
I.InvoiceDate,
I.Total,
IL.Quantity,
IL.UnitPrice
FROM CUSTOMER AS C, INVOICE AS I, INVOICELINE AS IL
WHERE C.CUSTOMERID = I.CUSTOMERID
AND I.INVOICEID = IL.INVOICEID;
The output produced would still end up with exactly the same number of records even though the queries are written completely different after the FROM clause. We can also see that we must specify all tables in the FROM clause separated by a comma instead of being after the JOIN condition. As a final validation we may want to strip the columns and replace them with the COUNT(*) to see that we get the same number of records.
Original:
Modified:
Both queries returned the same 2,240 records. This provides us some some peace of mind that these queries are actually doing the same thing. The extra minute it takes to validate queries is always worth the extra time.
Final Thoughts
For a beginner, this may seem easier to write. The problem is when introducing outer joins covered in the next section. Using Oracle databases (and some others) will allow using ‘(+)’ syntax to specify outer joins. Using these Oracle outer joins with the plus operand is NOT ANSI Standard syntax so be aware if you’re trying to use the (+) operator in your SQL queries.