SQL WHERE column = everything

Question

Is SQL able to do something along the lines of this: SELECT * FROM table WHERE column = everything

Answer 1

For anyone who NEEDS the column name in the query for whatever reason (probably dynamic SQL), a nice alternative would be SELECT * FROM table WHERE column = column

This is very similar to WHERE 1=1, however it includes the column name, which my solution required, and maybe a few others will require as well.

Answer 2

SELECT * FROM table

If you're generating SQL dynamically, it's

SELECT * FROM table WHERE 1=1

The 1=1 placeholder allows you to return all records, or substitute an actual condition if you're returning a subset or need additional conditional statements.

See Also
Why would someone use WHERE 1=1 AND <conditions> in a SQL clause?

Answer 3

Your question allow three intrepretations:

1. You don't care about the column: drop the it from the where clause (or drop the where clause alltogether, if this was the only subclause)
2. You want the column to be set, bit you don't care to what: Use WHERE column IS NOT nULL
3. You want a search, that can also display all records from a simple SQL template: SELECT * FROM table WHERE column LIKE '%$searchterm%'

Answer 4

You can use column name itself in your query:

SELECT * FROM TABLE WHERE COLUMN = COLUMN

for example:

SELECT * FROM Student WHERE YEAR = YEAR

or by using a Auxiliary parameter:

SELECT * FROM Student WHERE YEAR = (CASE WHEN @year IS NOT NULL THEN @year ELSE YEAR END)

so you can hold on "=" char

Answer 5

Everything or anything?

You could use a wildcard I guess.

SELECT * FROM table WHERE column LIKE "%"

Answer 6

SELECT * FROM table WHERE column = IF (? = '', column, ?);

Answer 7

This is late but may be helpful to others

You can try this.

where 
    isnull([column], '') = CASE WHEN @column IS NULL THEN isnull([column], '') ELSE @column END 

Answer 8

Are you perhaps looking for the IN criteria operator?

SELECT * from table where column in (1,2,3,4,5) or column in ('value', 'value2', 'value3');

Answer 9

Well I had this same issue too and the following solved my problem:

... where column = case when @variable = 'all' then column else @variable end

Keep it in mind that you must always send a default value , i set my default value as 'all'. So if i set @variable = 'all', mysql reads it as : where column = column which is the same thing as where 1=1

Answer 10

I've faced this problem while developing dynamically composing query. Here is my solution in short:

WHERE (column = ANY (SELECT distinct column) OR column IS NULL)

This works with NULL values and practically it is identical to empty WHERE statement. I use brackets in order to keep the ability to add more WHERE options using AND operator.

It means that this:

select count("objectId"), "source"
from "SomeTable"
where "createdAt" > '2020-07-06'
    and ("source" = any (select distinct "source") or "source" is null)
    and ("country" = any (select distinct "country") or "country" is null)
    and "channel" is not null
group by "source"

equals to this:

select count("objectId"), "source"
from "SomeTable"
where "createdAt" > '2020-07-06'
    and "channel" is not null
group by "source"

So I can make a query template:

...
    WHERE (column = {{filter_value}} )
...

and set ANY (SELECT distinct column) OR column IS NULL) as default value for {{filter_value}}

Answer 11

If this helps anyone... just to point out, if you have a problem such as SELECT * FROM something WHERE (can be specific or everything) such as filtering stuff, you can use
SELECT * FROM something as s WHERE (?1 = 0 OR ?1 = s.type = ?1)
0 here is a just predefined for all, so feel free to change, i needed this while using JPA repositories with hibernate for filtering. You can't do this medicinally as suggested by a previous answer because of the safety with prepared statements. Where the ?1 corresponds to :

  Page<Something> filterSometing(Long type,Pageable pageable);

Answer 12

put elvis operator to that field

" . ($variable == 'selected value' ? '' : "AND column='" . $variable . "' ") . "

so where u select all in the options in the html page this ^^ field will not run but when u select something this code will show up like column='".$variable."'

Answer 13

You can use

WHERE 1

1 is like in other language always true. So you have no filter

Answer 14

FOR PROCEURES: if you send a parameter for the condition, you can define a constant value to get all rows For example you can say when i send % for it get all rows

SELECT * FROM allstocks WHERE allstocks.storestockid=key1 OR '%'=key1

you can replace '%' with 1 or anything you want

Answer 15

If you want to do this, you basically need to use WHERE true. Since MySQL doesn't accept true though, you have to substitute it for a number.

As integers, 0 is equal to false and 1 is equal to true.

This means, if you want to achieve "WHERE everything", you can simply use:

WHERE 1

You can also use something that always outputs as true, such as:

WHERE 1=1

or

WHERE 1+2-1=2

You can also just remove the WHERE declaration in general:

SELECT * FROM table;

instead of

SELECT * FROM table
WHERE 1=1;