2

If I have a query like this

SELECT * FROM table1 WHERE col1 IN ({SUBS})

Is there anything I can replace {SUBS} with that will return all rows in the table?

Further details:

I am building the SQL dynamically in my app, so I cannot (should not) edit other parts of the query except what's in braces. So, 

SELECT * FROM table1

will not do.

Also, 

SELECT * FROM table1 WHERE col1 IN (SELECT col1 FROM table1)

would be hackish and highly inefficient. Consider the table have more than 50k rows.

Verhogen
  • 27,221
  • 34
  • 90
  • 109
  • 1
    I know this isn't the question, but I have to ask: are you just trying to get all of the rows? If so, just take off the "WHERE col1 IN ({SUBS})" part. – Donnie DeBoer Dec 09 '09 at 04:45
  • 1
    someone told me once - there's no such thing as a stupid question... – pstanton Dec 09 '09 at 04:46
  • 1
    sounds like someone is building dynamic sql to me... – marcc Dec 09 '09 at 04:56
  • "...would be highly inefficient. Consider the table have more than 50k rows." You said you wanted to retrieve all 50k rows. I think efficiency's kind of a moot point. – Dewayne Christensen Dec 09 '09 at 16:34
  • The question is, what is the [identity](http://en.wikipedia.org/wiki/Identity_(mathematics)) with respect to [semijoin](http://stackoverflow.com/questions/7033748/what-kind-of-join-do-i-need/7036894#7036894) in SQL? A good question which you answer yourself with your last query! – onedaywhen Mar 26 '12 at 08:42

7 Answers7

5

This would do it:

select col1 from table1

Edit: There seems to be a bit of confusion - the OP asked what value could be used to replace {SUBS} that would return all rows from table1. My answer above is what you could use in place of {SUBS} that would return all the rows.

Andrew Hare
  • 344,730
  • 71
  • 640
  • 635
  • 1
    you mean **select * from table1** – pstanton Dec 09 '09 at 04:46
  • 3
    Nope, I meant `select col1 from table1` :) – Andrew Hare Dec 09 '09 at 04:48
  • 2
    I think viligant is trying to find out if someone could use SQL injection to find all the data in the table, which Andrew's reply would allow, although I might use SELECT distinct col1 FROM table1 But his answer is proper – Sparky Dec 09 '09 at 04:56
  • From the posted question, it appears vigilant is trying to get full rows, not just col1. – Donnie DeBoer Dec 09 '09 at 05:01
  • @pstanton, @Donnie DeBoer: the answer is just the part to put in place of the .. the IN (...) – ysth Dec 09 '09 at 05:02
  • @Donnie DeBoer - Andrew meant replace {SUBS} with "select col1 from table1" – Chris Simmons Dec 09 '09 at 05:03
  • @Donnie DeBoer and @pstanton, what Andrew Hare means is that your should replace `{SUBS}` in the OP's question, with the SQL he posted in his answer which would result in getting all columns, not just `col1`. See my answer for an elaboration. – Asaph Dec 09 '09 at 05:04
  • @Sparky: I don't think the use of `DISTINCT` would be any more efficient. The query plan would in any case remove duplicates if deemed beneficial. Using `DISTINCT` essentially forces the query plan to always remove duplicates - even when not beneficial. – Disillusioned Dec 09 '09 at 08:19
3

This works for me in SQL Server:

SELECT * FROM INFORMATION_SCHEMA.COLUMNS
WHERE COLUMN_NAME IN (COLUMN_NAME)

Have you tried just using COL1 for {SUBS}?

e.g.

SELECT * FROM table1 WHERE col1 IN (col1)
beach
  • 8,330
  • 3
  • 29
  • 25
2

If you replaced {SUBS} with SELECT col1 FROM table1, you would end up with

SELECT * FROM table1 WHERE col1 IN (SELECT col1 FROM table1);

which would return all rows from table1. This is, of course, simply a more roundabout way of saying:

SELECT * FROM table1;
Asaph
  • 159,146
  • 25
  • 197
  • 199
1

You're right,

SELECT * FROM table1 WHERE col1 IN (SELECT col1 FROM table1)

does work, but is highly inefficient; requiring a merge join to return all rows.

Use the following which is just as efficient as regular SELECT * FROM table1

SELECT * FROM table1 WHERE col1 IN (col1)

However, that said; I suggest you have a chat to the person who is trying to impose the SELECT * FROM table1 WHERE col1 IN ({SUBS}) structure. There is no good reason to do so.

  • It unnecessarily complicates queries.
  • Creates risk of highly inefficient queries.
  • Potentially even limits developers to use certain techniques.

I suspect the person imposing this is trying to implement some sort of silver-bullet framework. Remember, the golden rule in software development is that there are no silver-bullets.

Disillusioned
  • 14,635
  • 3
  • 43
  • 77
0

If you're simply trying to retrieve every row in the table, then:

select * from table1

If you're trying to prove a point or win a bet or something, then:

select * from table1 where col1 in (select col1 from table1)
Dewayne Christensen
  • 2,084
  • 13
  • 15
0

If the query requires some WHERE condition, then I would try to replace it with an EXISTS statement:

select
  *
from
  table1 t1
where
  exists ( {subs} )

Then {subs} can be replaced with any expression that does not yield NULL.

Quick Joe Smith
  • 8,074
  • 3
  • 29
  • 33
0

This works in Oracle:

select * from table1 where col1 in (col1)
Mosty Mostacho
  • 42,742
  • 16
  • 96
  • 123
Khb
  • 1,423
  • 9
  • 9