CODEWITHSUNDEEP
sqlsubquery
Verhogen
Verhogen

Reputation: 28631

SQL "In" Statement Match Anything

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.

Upvotes: 2

Views: 263

Answers (7)

Khb
Khb

Reputation: 1433

This works in Oracle:

select * from table1 where col1 in (col1)

Upvotes: 0

Quick Joe Smith
Quick Joe Smith

Reputation: 8232

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.

Upvotes: 0

Disillusioned
Disillusioned

Reputation: 14832

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.

Upvotes: 1

beach
beach

Reputation: 8640

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)

Upvotes: 3

Andrew Hare
Andrew Hare

Reputation: 351546

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.

Upvotes: 5

Dewayne Christensen
Dewayne Christensen

Reputation: 2094

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)

Upvotes: 0

Asaph
Asaph

Reputation: 162821

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;

Upvotes: 2

Related Questions