'SQL WHERE column = everything

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

mysqlsql


Solution 1:[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.

Solution 2:[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?

Solution 3:[3]

Everything or anything?

You could use a wildcard I guess.

SELECT * FROM table WHERE column LIKE "%"

Solution 4:[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

Solution 5:[5]

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

Solution 6:[6]

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

Solution 7:[7]

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

Solution 8:[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');

Solution 9:[9]

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}}

Solution 10:[10]

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);

Solution 11:[11]

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."'

Solution 12:[12]

You can use

WHERE 1

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

Solution 13:[13]

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

Related Questions