SQL WHERE column = everything
MysqlSqlMysql Problem Overview
Is SQL able to do something along the lines of this: SELECT * FROM table WHERE column = everything
Mysql Solutions
Solution 1 - Mysql
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 - Mysql
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.
Solution 3 - Mysql
Your question allow three intrepretations:
- 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)
- You want the column to be set, bit you don't care to what: Use
WHERE column IS NOT nULL
- You want a search, that can also display all records from a simple SQL template:
SELECT * FROM table WHERE column LIKE '%$searchterm%'
Solution 4 - Mysql
Everything or anything?
You could use a wildcard I guess.
SELECT * FROM table WHERE column LIKE "%"
Solution 5 - Mysql
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 6 - Mysql
SELECT * FROM table WHERE column = IF (? = '', column, ?);
Solution 7 - Mysql
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 8 - Mysql
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 - Mysql
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 10 - Mysql
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 11 - Mysql
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 12 - Mysql
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 13 - Mysql
You can use
WHERE 1
1 is like in other language always true. So you have no filter
Solution 14 - Mysql
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