Confused about Subqueries grammer.
-
:confused:When I read the book "teach yourself SQL in 21 days", day 7, first example:: SELECT * FROM TABLE1 WHERE TABLE1.SOMECOLUMN = (SELECT SOMEOTHERCOLUMN FROM TABLE2 WHERE SOMEOTHERCOLUMN = SOMEVALUE) When I tried this subquery, it doesn't work in SQL-server 2000 at all ! After that I searched online, then found that I have to use "IN" instead of "=" to express the subquery. I am confused ! Why the book writes like that? Is this an old SQL ? SQL is supposed to be universal, why I cannot use above example in SQL2000? I also found some other places which is not wworking in Sql-server 2000 environment. Please help me on this silly question. Thanks Dennis
-
:confused:When I read the book "teach yourself SQL in 21 days", day 7, first example:: SELECT * FROM TABLE1 WHERE TABLE1.SOMECOLUMN = (SELECT SOMEOTHERCOLUMN FROM TABLE2 WHERE SOMEOTHERCOLUMN = SOMEVALUE) When I tried this subquery, it doesn't work in SQL-server 2000 at all ! After that I searched online, then found that I have to use "IN" instead of "=" to express the subquery. I am confused ! Why the book writes like that? Is this an old SQL ? SQL is supposed to be universal, why I cannot use above example in SQL2000? I also found some other places which is not wworking in Sql-server 2000 environment. Please help me on this silly question. Thanks Dennis
First Be Carefull, u can use both of them but there is exceptions. SELECT * FROM TABLE1 WHERE TABLE1.SOMECOLUMN = (SELECT SOMEOTHERCOLUMN FROM TABLE2 WHERE SOMEOTHERCOLUMN = SOMEVALUE) command is TRUE but "SELECT SOMEOTHERCOLUMN FROM TABLE2 WHERE SOMEOTHERCOLUMN = SOMEVALUE" will be produce JUST ONE RESULT, NOT MORE. So to use any aggregation Function in this case is more true. For Example; USE NORTHWIND select * from Orders where CustomerID =(select MAX(CustomerID) from Customers) In this case, not just (=) equals sign, Also u cant use (=!,>,<,>=,<=,LIKE) But some times we get more result from 2nd command.In this case u have to use IN keyword. For Example, U need Orders information belongs to Customers in London. if u execute "SELECT * FROM Customers WHERE City ='London'" u see 6 records. then write command for our first aim : USE NORTHWIND select * from Orders where CustomerID IN (select CustomerID from Customers WHERE City='London') I hope u got what u need. So u can get a result set that contains six different customers' Orders Informations.