WHERE table1.column1 = table2.column2 (does the order of assignment matter?)

Question

WHERE table1.column1 = table2.column2 (does the order of assignment matter?)

michael.leach2015

SSCommitted

Points: 1597
More actions
May 19, 2020 at 11:08 pm

#3753101
I just started reading about the EXISTS operator. I have two examples. In both examples I have a sub SELECT with a WHERE clause. In the first example, at the inner WHERE clause I am referencing the "inner" table first, which is called orders, then the customer_id column within the orders table. Then I have the assignment operator followed by a reference to the "outer" table called customer, followed by the customer_id column in the customer table.
```
SELECT  customer_id, cust_name, city
FROM    customer
WHERE   EXISTS (SELECT 1    FROM orders    WHERE orders.customer_id = customer.customer_id);
```
In my second example, the condition in the "inner" WHERE clause is flipped around, i.e. WHERE customer.customer_id = orders.customer_id. Here the "outer" table is referenced first, then the "inner" table.
```
SELECT  customer_id, cust_name, city
FROM    customer
WHERE   EXISTS (SELECT 1    FROM orders    WHERE customer.customer_id = orders.customer_id);
```
Both of these queries gives me the same result. However I am curious to know if it really matters if I reference the "inner" table or the "outer" table first at the "inner" WHERE clause.
If we look at the first example, the "inner" WHERE clause is WHERE orders.customer_id = customer.customer_id. How would you read this? I am thinking that it would be something such as, "For each row in the orders table, under the customer_id column, does that value exist in the customer_id column of the customer table? Then go to the 2nd row and ask the same question. Then the third row, etc." I believe that EXISTS would stop executing the sub SELECT as soon as one row is returned, but is this the right idea?
In otherwords, is it true to say (in this case or any other case where we have one column assigned to another column) that we start with the column (actually a value in that column) on the left of the = sign, then we take the column (actually a value in that column) on the right side of the = sign, and see if it matches to the column (value) on the left side of the = sign?

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

frederico_fonseca SSCoach Points: 16153 More actions · Answer 1

order does not matter - and the way it works "logically" is that for each row on the out query (customer) check if a row exists on the inner query (Orders). Fact you change the order of the columns within the where has no bearing on what the sql is doing

Jeff Moden SSC Guru Points: 1004304 More actions · Answer 2

That's for equalities. If you have an inequality, you'll need to reverse the comparison operator if you reverse the order of the columns.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

jcelko212 32090 SSCrazy Eights Points: 9303 More actions · Answer 3

>> just started reading about the EXISTS() operator. I have two examples. In both examples, I have a sub SELECT with a WHERE clause. In the first example, at the inner WHERE clause I am referencing the "inner" table first, which is called Orders, then the customer_id column within the Orders table. Then I have the assignment operator followed by a reference to the "outer" table called customer, followed by the customer_id column in the customer table.

SELECT customer_id, cust_name, city_name
FROM Customers -- you do have more than one?
WHERE EXISTS
(SELECT 1 -- not the best practice
FROM Orders
WHERE Orders.customer_id = Customers.customer_id);

This is not quite how it works. EXISTS () is a predicate that can return only true or false, never unknown. I see that you use the "SELECT <expression>" syntax. The preference is to use "SELECT *" instead of the old Oracle syntax. The * has been our symbol to represent an entire row, as a unit. Think about COUNT(*).

The first Oracle SQL engines actually worked out the list! If you think about it for two seconds, this makes no sense. Later, we realize the SELECT list meant nothing. The optimizers handling the EXISTS () predicate simply worked around it. All you needed was one match and you could stop processing. The WHERE clause predicate inside the EXISTS () is not really a sub-SELECT.

>> In my second example, the condition in the "inner" WHERE clause is flipped around, i.e. WHERE Customerscustomer_id = Orders.customer_id. Both of these queries give me the same result. However, I am curious to know if it really matters if I reference the "inner" table or the "outer" table first at the "inner" WHERE clause. <<

It doesn't matter. The optimizer will rearrange the query.

>> Then go to the 2nd row and ask the same question. Then the third row, etc." I believe that EXISTS would stop executing the sub SELECT as soon as one row is returned, but is this the right idea? <<

Yes, but it can be a little bit better than that by looking at indexes and so on. There's a good chance it won't touch one or both of the base tables

This reply was modified 4 years, 5 months ago by Steve Jones - SSC Editor. Reason: code formatting

Please post DDL and follow ANSI/ISO standards when asking for help.