How Implicit inner join works with multiple WHERE clause conditions

With Explicit Inner join, we specify the common column names from both tables and it selects records that have matching values in these columns.

SELECT Customers.customer_id, Customers.name, Orders.amount
FROM Customers
INNER JOIN Orders
ON Customers.customer_id = Orders.customer_id
WHERE Orders.amount >= 200;

But with Implicit Inner join, we just use the WHERE clauses and specify the conditions to be the match. So I want to understand how joining works for multiple conditions, Does the order of conditions matter?

I am asking this because I have two tables like the below:-

Table1: Customers

Table2: Orders

The customer_id field in the orders table stores the values of the customer_id field of the customers table. And unfortunately, we don't have any foreign key constraint on the customer_id field of the orders table. So if you see the above orders table, the customer_id=5 doesn't exist in the customers table and when I run the below query, it returns the below result.

Query:-

SELECT Customers.customer_id, Orders.customer_id, Customers.name, Orders.amount
FROM Customers, Orders
WHERE Customers.customer_id = Orders.customer_id
AND Orders.amount >= 200;

Result:-

All the field's data from the table Orders is being fetched and the field's data for the customers table is returned as empty data. But ideally inner join only returns the data if there is a match from both tables?

I might be misunderstanding something and clearing my basics.

Thanks for the help!

Update:

I really apologize for this but I just noticed in the code there is (+) on the right side of the WHERE clause which I was excluded thinking is of no use and now I came to know it denotes RIGHT OUTER join. And now the query results make sense to me.

SELECT Customers.customer_id, Orders.customer_id, Customers.name, Orders.amount
FROM Customers, Orders
WHERE Customers.customer_id = Orders.customer_id (+)
AND Orders.amount >= 200;

Thanks to the StackOverflow community, you are the best!

Your query:

SELECT Customers.customer_id, Orders.customer_id, Customers.name, Orders.amount
FROM Customers, Orders
WHERE Customers.customer_id = Orders.customer_id
AND Orders.amount >= 200;

Then the legacy comma-join can be replaced with an ANSI-standard CROSS JOIN and is the same as:

SELECT c.customer_id, o.customer_id, c.name, o.amount
FROM   Customers c CROSS JOIN Orders o
WHERE  c.customer_id = o.customer_id
AND    o.amount >= 200;

Which, in turn, is the same as:

SELECT c.customer_id, o.customer_id, c.name, o.amount
FROM   Customers c
       INNER JOIN Orders o
       ON c.customer_id = o.customer_id
WHERE  o.amount >= 200;

Which is the same as your first query.

> Does the order of conditions matter?

No

> when I run the below query, it returns the below result.

That result is impossible as Customers.customer_id = Orders.customer_id is not true in the output so the result cannot be returned.

Your query:

SELECT Customers.customer_id, Orders.customer_id, Customers.name, Orders.amount
FROM Customers, Orders
WHERE Customers.customer_id = Orders.customer_id
AND Orders.amount >= 200;

cannot produce the output you showed. This is an inner join, and since there is no matching customer_id = 5 in the customers table, it will not show the order record for customer_id 5 either. If you wanted to see it, you'd need an outer join:

SELECT Customers.customer_id, Orders.customer_id, Customers.name, Orders.amount
FROM Customers, Orders
WHERE Customers.customer_id(+) = Orders.customer_id
AND Orders.amount >= 200;

To answer your specific question, the order of join clauses does not matter, nor the order of columns in the join clause. Join predicates are treated the same as filter predicates and evaluated in any order Oracle pleases without changing the result. There's something wrong with your test. Please double-verify your SQL and its results.

Did you run your own code ? It doesn't show the results you posted.

SQLcl: Release 22.4 Production on Mon Apr 17 15:10:13 2023
koen>CREATE TABLE customers (customer_id,name,age)
  2  AS
  3  (
  4  SELECT 1,  'John', 25 FROM DUAL UNION ALL
  5  SELECT 2,  'Marry',    22 FROM DUAL 
  6* );

Table CUSTOMERS created.

koen>CREATE TABLE orders (order_id,amount,customer_id) 
  2  AS
  3  (
  4  SELECT 1,  100,    1 FROM DUAL UNION ALL
  5  SELECT 2,  200,    5 FROM DUAL
  6* );

Table ORDERS created.

Your query - using traditional join syntax.

koen>SELECT Customers.customer_id, Orders.customer_id, Customers.name, Orders.amount
  2  FROM Customers, Orders
  3  WHERE Customers.customer_id = Orders.customer_id(+)
  4* AND Orders.amount >= 200;

no rows selected

same query rewritten using ANSI join syntax

koen>SELECT Customers.customer_id, Orders.customer_id, Customers.name, Orders.amount
  2    FROM Customers
  3         JOIN Orders ON Customers.customer_id = Orders.customer_id
  4*   WHERE Orders.amount >= 200;

no rows selected

Query rewritten to produce the results you display - using traditional join syntax.

koen>SELECT Customers.customer_id, Orders.customer_id, Customers.name, Orders.amount
  2  FROM Customers, Orders
  3  WHERE Customers.customer_id(+) = Orders.customer_id
  4* AND Orders.amount >= 200;

   CUSTOMER_ID    CUSTOMER_ID NAME       AMOUNT 
______________ ______________ _______ _________ 
                            5               200 

same query rewritten using ANSI join syntax

koen>SELECT customers.customer_id, orders.customer_id, customers.name, orders.amount
  2    FROM orders
  3         left outer JOIN customers ON customers.customer_id = orders.customer_id
  4*   WHERE orders.amount >= 200;

   CUSTOMER_ID    CUSTOMER_ID NAME       AMOUNT 
______________ ______________ _______ _________ 
                            5               200