Multiple Columns in a Sub Select??
Selecting more than 1 column in a sub-select is possible. I learned this tip from Jochem van Dieten (who knows more about SQL than anyone I know) on a recent CF-Talk thread. According to Jochem you can use the following Syntax:
<Cfquery ... >
SELECT * FROM table
WHERE (x, y) IN
(SELECT a, b FROM otherTable)
</CFQUERY>
...Now ya gotta admit that's pretty neat!
SELECT * FROM table
WHERE (x, y) IN
(SELECT a, b FROM otherTable)
</CFQUERY>
Note: This syntax will not work on every db server. It will not work on MS SQL server for example. See the notes below for more detail
24 Comments
-
MS-SQL 2000 doesn't seem to think so:
---
IN
Determines if a given value matches any value in a subquery or a list.
Syntax
test_expression [ NOT ] IN
(
subquery
| expression [ ,...n ]
)
Arguments
test_expression
Is any valid Microsoft® SQL Server™ expression.
subquery
Is a subquery that has a result set of one column. This column must have the same data type as test_expression.
expression [,...n]
Is a list of expressions to test for a match. All expressions must be of the same type as test_expression.
---
Notice the fact that the subquery has a result set of ONE column. -
Yes - it appears you are right. I tested it with a table and it errored out. I'll find out what platforms are compliant and do an update. -
It is very well possible that some implementations do not support this feature. However, it is part of the official SQL standard (ISO/IEC 9075-2:2003 section 8.4) and there are several implementations that support it. If you want to play with it, try PostgreSQL. -
Jochem,
I was hoping you would chime in before I had to go slogging through documentation :) PostgreSQL and MySQL are probably a "go" then... I wonder about Oracle? -
I can't seem to find any page that just shows me that standard (except if I buy a copy), but am I correct if this would be some kind of a short-hand for:
SELECT
*
FROM
table
WHERE
x IN ( SELECT a FROM otherTable ) AND
y IN ( SELECT b FROM otherTable ) -
Auke,
You are correct as far as it goes. But the advantage of doing it in one statement would be a much leaner execution plan. The engine would have to apply a single WHERE clause to a subset - instead of 2 separate where clauses to 2 subsets. -
Yeah I was thinking the same thing about the execution plan, and of course that all depends on how the WHERE ( a, b ) IN .. is implemented 'under the hood'. But I guess it would have been optimized :-).
It has to be said though, that I hardly ever need to do anything just like this. I don't often have two relationships between the same tables defined, and therefore have no need to build a query like this as well. And IF I need something similar, I normally need the output from 'otherTable' as well:
SELECT
*
FROM
table INNER JOIN otherTable ON ( x = a AND y = b )
Which does *not* result in the same set of course, just stating that both relations need to 'comply'. -
SELECT *
FROM table
WHERE
x IN ( SELECT a FROM otherTable ) AND
y IN ( SELECT b FROM otherTable )
This is not the same as the posted query. In this query a and b are not required to be from the same row. The correct translation would be:
SELECT *
FROM table INNER JOIN otherTable ON
( table.x = otherTable.a AND table.y = otherTable.b)
This would be the recommended way of writing the query anyway because it is easier on the optimizer. You frequently need this type of query when you have composite primary keys.
BTW The SQL standard is available for free in some libraries, such as the library of Delft University of Technology. -
Actually this example is pretty easy to do within the joins:
SELECT *
FROM
table t
INNER JOIN otherTable ot
ON t.x = ot.a
AND t.y = ot.b -
Tony,
Yes .... it is simple to do with joins, but it also provides a simple example, which is the point. -
@mkruger
touche. The an example was a simple one. For more complex queries you can dervied table to get the results. Also you can do the smeaky method to use one or more columns
SELECT *
FROM table t
WHERE
t.pk IN (
SELECT ot.fk
FROM othertable ot
WHERE
t.x = ot.a
AND t.y = ot.b
) -
Works on Oracle 10g, I just tried it.
Thanks for the very cool tip! -
I do have a need for such a query. How could you translate the following statement using the recommended syntax:
SLECT * FROM table
WHERE (x, y) IN
(select x, max(y) FROM table GROUP BY x);
Oracle is complaining with the ORA-00920: invalid relational operator. -
Andy, try this:
select a.* from table a inner join
(select x, max(y) as y from table group by x) b
on a.x = b.x and a.y = b.y;
I've used this on MS-SQL 2000. Haven't verified it works on others. -
Chris, I guess you could do this
select a.* from table a inner join
(select x, max(y) as y from table group by x) b
on a.x <> b.x and a.y <> b.y;
It should be possible to substitute the 'and' operator by 'or' if you you want any of the conditions to cause an exclusion. -
In the past, I have matched multi-column keys between tables using concatenation. The problem is that this prevents the keys indexes from being used. Try this:
SELECT * FROM table
WHERE (x+y) IN
(SELECT x+max(y)
FROM table
GROUP BY x); -
select * from table_name where (column1+column2) in (select column1+column2 from table_name) -
THe below does not give you the same results as we want, it will give you all distinct x s as a and all distinct y s as b but we need two use the two conditions on the same row.
SELECT *
FROM table
WHERE
x IN ( SELECT a FROM otherTable ) AND
y IN ( SELECT b FROM otherTable )
the correct one is as below...and the below did work for me on oracle..andy said it did not work for him but i just executed now now...
SLECT * FROM table
WHERE (x, y) IN
(select x, max(y) FROM table GROUP BY x); -
put a left/right join between two tables and
in the where put field = null like this:
SELECT
A.field1,
A.field2
FROM
B RIGHT OUTER JOIN
A ON B.field1 = A.field1 AND
B.field2 = A.field2
WHERE
(B.field1 IS NULL) AND
(B.field1 IS NULL)
saludos desde Bog. Col. -
Hi, the last post was a bit old but maybe there is still someone to help me.
I try to link 2 tables using two columns and one condition but I can't make it work and I didn't get how you used the group by function
Here's my code:
select *
from Tbl_Observers
where
Tbl_Observers.LocationID in (select Tbl_Field.LocationID from Tbl_Field where Tbl_Observers.LocationID='CO')
and Tbl_Observers.SurveyID in (select Tbl_Field.SurveyID from Tbl_Field)
and Tbl_Observers.ObserverCode='H_Edwards'
Can you help me please -
Where this is really useful (and hence a real pain that you can't do it in MSSQL) is in a delete.
Delete from tbl1
where (col1, col2) in
(select col1, col2 from tbl1 where.....)
Is a really useful construct.
Sites to Watch
Aggregators
RSS
