March 7, 2009 at 9:58 am
these 2 SELECT return the same result, but do they have the same meaning? Can I put the WHERE condition in FROM?
CREATE TABLE dbo.Customers
(
customerid CHAR(5) NOT NULL PRIMARY KEY,
city VARCHAR(10) NOT NULL
);
CREATE TABLE dbo.Orders
(
orderid INT NOT NULL PRIMARY KEY,
customerid CHAR(5) NULL REFERENCES Customers(customerid)
);
GO
INSERT INTO dbo.Customers(customerid, city) VALUES('CUST1', 'NEW YORK');
INSERT INTO dbo.Customers(customerid, city) VALUES('CUST2', 'NEW YORK');
INSERT INTO dbo.Customers(customerid, city) VALUES('CUST3', 'NEW YORK');
INSERT INTO dbo.Customers(customerid, city) VALUES('CUST4', 'BOSTON');
INSERT INTO dbo.Orders(orderid, customerid) VALUES(1, 'CUST2');
INSERT INTO dbo.Orders(orderid, customerid) VALUES(2, 'CUST2');
INSERT INTO dbo.Orders(orderid, customerid) VALUES(3, 'CUST3');
INSERT INTO dbo.Orders(orderid, customerid) VALUES(4, 'CUST3');
INSERT INTO dbo.Orders(orderid, customerid) VALUES(5, 'CUST3');
INSERT INTO dbo.Orders(orderid, customerid) VALUES(6, 'CUST4');
INSERT INTO dbo.Orders(orderid, customerid) VALUES(7, NULL);
SELECT C.customerid, O.orderid AS numorders
FROM dbo.Customers AS C
LEFT OUTER JOIN dbo.Orders AS O
ON C.customerid = O.customerid and C.city = 'NEW YORK'
WHERE orderid is not NULL
SELECT C.customerid, O.orderid AS numorders
FROM dbo.Customers AS C
LEFT OUTER JOIN dbo.Orders AS O
ON C.customerid = O.customerid
WHERE C.city = 'NEW YORK' and orderid is not NULL
March 7, 2009 at 10:41 am
No, you can't swap the conditions between FROM and WHERE. In your same query, get the second condition (orderid not null) to the join clause, you will see the difference in results.
SELECT C.customerid, O.orderid AS numorders
FROM dbo.Customers AS C
LEFT OUTER JOIN dbo.Orders AS O
ON C.customerid = O.customerid and orderid is not NULL
WHERE C.city = 'NEW YORK'
---------------------------------------------------------------------------------
March 7, 2009 at 11:28 am
Thanks. Is there any case that we need to put the WHERE condition in FROM? I have a developer did it, and I am not convinced he could do it.
March 7, 2009 at 12:35 pm
Hello
It is possible to swap conditions between FROM and WHERE if you are using an INNER JOIN. This makes sometimes sense for a faster execution.
But sure there are also own business cases which require to specify criteria within the FROM/JOIN section. Example
Give me all customers which ever had orders with a price of more than 100.
[font="Courier New"]
DECLARE @customer TABLE (id INT, name VARCHAR(MAX))
DECLARE @order TABLE (id INT, customer_id INT, price MONEY)
INSERT INTO @customer VALUES (1, 'Mikey Mouse')
INSERT INTO @customer VALUES (2, 'Garfield')
INSERT INTO @order VALUES (1, 1, 12)
INSERT INTO @order VALUES (2, 2, 34)
INSERT INTO @order VALUES (3, 2, 120)
SELECT c.*
FROM @customer c
LEFT JOIN @order o ON c.id = o.customer_id AND o.price > 100
WHERE o.id IS NOT NULL
[/font]
Greets
Flo
March 7, 2009 at 12:36 pm
HI Yihong,
IMO, they are same however that said I am against the first styles. In that example because it makes it hard to read the SQL Statement. THis is a simple join between two tables think about a join between 5, 10, 15, or more tables it going to be nightmare to manage what is a where condition and what is a join condition.
I generally recommend if it is not joining two tables together then it should be in where clause because it is a filter statement and not join. Only thing that should follow ON clause is table1.filed = table2.field; if there are two fields being joined then it should be table1.filed1 = table2.field1 and table1.field2 = table2.field. In this case it is still a join condition and not filter condition.
Good Example:
SELECT *
FROM table1 AS T1
INNER JOIN tabl2 AS T2
ON T1.field1 = T2.field1
AND T1.field2 = T2.field2
WHERE T1.field1 = 'John'
AND T2.field3 > 0
Bad example:
SELECT *
FROM table1 AS T1
INNER JOIN tabl2 AS T2
ON T1.field1 = T2.field1
AND T1.field1 = 'Jon'
AND T2.field3 > 0
Makes sense?
Mohit.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 7, 2009 at 12:46 pm
Hi Mohit
No, they are not always same. If you use a LEFT JOIN the criteria within the JOIN part will only be used to restrict the right side.
I extended my customers 🙂 . Just copy and execute:
[font="Courier New"]DECLARE @customer TABLE (id INT, name VARCHAR(MAX))
DECLARE @order TABLE (id INT, customer_id INT, price MONEY)
INSERT INTO @customer VALUES (1, 'Mikey Mouse')
INSERT INTO @customer VALUES (2, 'Garfield')
INSERT INTO @customer VALUES (3, 'Donald Duck')
INSERT INTO @customer VALUES (3, 'Daisy Duck')
INSERT INTO @order VALUES (1, 1, 12)
INSERT INTO @order VALUES (2, 2, 34)
INSERT INTO @order VALUES (3, 2, 120)
-- Correct
SELECT *
FROM @customer c
LEFT JOIN @order o ON c.id = o.customer_id
WHERE c.name LIKE '%Duck'
-- Incorrect
SELECT *
FROM @customer c
LEFT JOIN @order o ON c.id = o.customer_id AND c.name LIKE '%Duck'
[/font]
Greets
Flo
March 7, 2009 at 1:05 pm
Heh Thanks :). I got alot to learn about T-SQL .. meh but then I got lot to learn about DBA stuff too 😛 Thus the reason of me wondering around on all the forms. I don't get enough to do at work to learn all the neet tricks and tips :). Thanks.
Another thing I wanna know is how you get all the fancy colors in your code post? I can't seem to get that :(.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 7, 2009 at 1:24 pm
Yeah there is no other language more flexible than SQL... I'm doing that since about ten years now and I'm still learning 🙂
For the SQL formatting:
I saw this first time in a post of Lowell (thanks again!). He sent me a link to a simple web-based tool on this page http://extras.sqlservercentral.com/prettifier/prettifier.aspx . Just put your SQL into the text area, select option "IFCode" in the left sidebar and press "Prettify!".
Greets
Flo
March 7, 2009 at 2:27 pm
florian.reischl (3/7/2009)
Hi MohitNo, they are not always same. If you use a LEFT JOIN the criteria within the JOIN part will only be used to restrict the right side.
I extended my customers 🙂 . Just copy and execute:
[font="Courier New"]DECLARE @customer TABLE (id INT, name VARCHAR(MAX))
DECLARE @order TABLE (id INT, customer_id INT, price MONEY)
INSERT INTO @customer VALUES (1, 'Mikey Mouse')
INSERT INTO @customer VALUES (2, 'Garfield')
INSERT INTO @customer VALUES (3, 'Donald Duck')
INSERT INTO @customer VALUES (3, 'Daisy Duck')
INSERT INTO @order VALUES (1, 1, 12)
INSERT INTO @order VALUES (2, 2, 34)
INSERT INTO @order VALUES (3, 2, 120)
-- Correct
SELECT *
FROM @customer c
LEFT JOIN @order o ON c.id = o.customer_id
WHERE c.name LIKE '%Duck'
-- Incorrect
SELECT *
FROM @customer c
LEFT JOIN @order o ON c.id = o.customer_id AND c.name LIKE '%Duck'
[/font]
Greets
Flo
Hello Flo,
Thanks, thats a good example.
I think one of the reason why developers would prefer to put theirs 'filter' condition in the JOIN clause is that they are used to old-style (SQL-89) Join syntax as given below and would give the desired result.
Hello YiHong, I am just guessing it could be one of the reason and I dont see any vaid reason to put it in the Join Condition.
DECLARE @customer TABLE (id INT, name VARCHAR(MAX))
DECLARE @order TABLE (id INT, customer_id INT, price MONEY)
INSERT INTO @customer VALUES (1, 'Mikey Mouse')
INSERT INTO @customer VALUES (2, 'Garfield')
INSERT INTO @customer VALUES (3, 'Donald Duck')
INSERT INTO @customer VALUES (3, 'Daisy Duck')
INSERT INTO @order VALUES (1, 1, 12)
INSERT INTO @order VALUES (2, 2, 34)
INSERT INTO @order VALUES (3, 2, 120)
-- Correct
SELECT *
FROM @customer c
LEFT OUTER JOIN @order o ON c.id = o.customer_id
WHERE c.name LIKE '%Duck'
-- SQL-89 syntax
SELECT *
FROM @customer c,@order o WHERE c.id *= o.customer_id AND c.name LIKE '%Duck%'
Thanks.
Pakki
---------------------------------------------------------------------------------
March 7, 2009 at 2:35 pm
Yaa probably "c.id *= o.customer_id" causes me alot of heach though when upgrading old SQL 6.5 applications to newer version. I always always trying to talk to my friend to convince him to use new format. He just stick in his ways; so I'll have to deal with it when I upgrade everything to 2005, or later :).
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 7, 2009 at 2:37 pm
florian.reischl (3/7/2009)
Yeah there is no other language more flexible than SQL... I'm doing that since about ten years now and I'm still learning 🙂For the SQL formatting:
I saw this first time in a post of Lowell (thanks again!). He sent me a link to a simple web-based tool on this page http://extras.sqlservercentral.com/prettifier/prettifier.aspx . Just put your SQL into the text area, select option "IFCode" in the left sidebar and press "Prettify!".
Greets
Flo
W00t! Thanks. I don't like one color code it drives me nuts I can finally fix code on my blog I hope it works *_*.
Mohit K. Gupta, MCITP: Database Administrator (2005), My Blog, Twitter: @SQLCAN[/url].
Microsoft FTE - SQL Server PFE
* Some time its the search that counts, not the finding...
* I didn't think so, but if I was wrong, I was wrong. I'd rather do something, and make a mistake than be frightened and be doing nothing. :smooooth:[/font]
March 7, 2009 at 3:18 pm
Mohit (3/7/2009)
Yaa probably "c.id *= o.customer_id" causes me alot of heach though when upgrading old SQL 6.5 applications to newer version. I always always trying to talk to my friend to convince him to use new format. He just stick in his ways; so I'll have to deal with it when I upgrade everything to 2005, or later :).
😀
Furtunately I don't have this problems. I'm the technical project leader and specified the design rule that the old style joins are not allowed. 😛
March 7, 2009 at 3:46 pm
florian.reischl (3/7/2009)
Mohit (3/7/2009)
Yaa probably "c.id *= o.customer_id" causes me alot of heach though when upgrading old SQL 6.5 applications to newer version. I always always trying to talk to my friend to convince him to use new format. He just stick in his ways; so I'll have to deal with it when I upgrade everything to 2005, or later :).😀
Furtunately I don't have this problems. I'm the technical project leader and specified the design rule that the old style joins are not allowed. 😛
The old style outer joins are not allowed, but equi-joins, which are equivalent to INNER JOINs, will probably never be removed; if you did, you'd have to remove EXISTS\NOT EXISTS, and others.
To the original question - definitely not the same. Only inner joins you can add this. Throwing conditions that should go into the WHERE clause can sometimes be confusing as well, so leaving them there might help with consistency.
Just my .02 cents 😀
Lee
March 7, 2009 at 8:22 pm
Thanks to all of you
florian.reischl, your example is exactly what happened at my work. The query needs to join 6 tables together (I agree, it's very difficult to read with old fashion style), with your first correct query, we can't get Mikey Mouse & Garfield, but we need these 2 records, that was why a developer came up the second query. how can we get Mikey Mouse & Garfield in with your first query?
March 7, 2009 at 8:59 pm
florian.reischl, in your second query,
SELECT *
FROM @customer c
LEFT JOIN @order o ON c.id = o.customer_id AND c.name LIKE '%Duck'
is the filter c.name LIKE '%Duck' actually be evaluated?
Viewing 15 posts - 1 through 15 (of 17 total)
You must be logged in to reply to this topic. Login to reply