Is it possible to use "WHERE" clause to select all records in SQL Statement?

Question

Good Evening, I am curious if it is possible to make a WHERE-clause in a SQL statement which can show all records?

Below some explanation:

Random SQL Statement (Java)-(JSP example), Normal Situation

String SqlStatement = "SELECT * FROM table_example WHERE First_Col = '<%=passVar%>' ";
db.query(SqlStatement );
//........
//........

What if the passVar is 'ALL', and we need to prompt all the records out when passVar = All? I know I can do it with if-else and check if the passVar is "ALL" then query the without-WHERE statement to make it work..

**without-WHERE statement (Java)-(JSP example)**

if(<%=passVar%> == "ALL") {
   SqlStatement = "SELECT * FROM table_example";
} else {

   SqlStatement = "SELECT * FROM table_example WHERE First_Col = '<%=passVar%>' ";
}

but can I just code one SQL statement to make all the records prompt? Something like below:

(Java)-(JSP example)

String ShowAll = "";
if(<%=passVar%> == "ALL") {
    ShowAll = *;

} else {
    ShowAll = <%=passVar%>;
}
SqlStatement = "SELECT * FROM table_example WHERE First_Col = ShowAll ";

You still put scriptlet code in JSPs? Database calls? Terrible. — duffymo, Oct 17 '13 at 11:25
lol, i just can followed the rules in my company... really sorry... — 薛源少, Oct 18 '13 at 01:32

score 14 · Answer 1 · answered Oct 17 '13 at 11:21

14

Try with WHERE 1=1::

Select * from myTable  WHERE 1=1

answered Oct 17 '13 at 11:21

Sashi Kant

13,277
9
44
71

How can the OP insert that in the script to use only one query? – juergen d Oct 17 '13 at 15:58

score 4 · Accepted Answer · answered Oct 17 '13 at 11:26

4

This also works:

WHERE columnname LIKE '%'

Except for NULL values.

answered Oct 17 '13 at 11:26

Pradeep Simha

17,683
18
56
107

Thanks, I will try on it... Just to verify that, if we make the columnname LIKE '%', whether it will retrieve data which start with ' ' (space)? – 薛源少 Oct 18 '13 at 01:45
Yes, it will do that – Baksteen Feb 28 '18 at 10:53
1

Is there way to include NULL values too? – Entwickler Aug 19 '19 at 18:12

score 3 · Answer 3 · edited Jan 04 '20 at 11:44

3

where 1=1 worked for me, Although where clause was being used all records were selected.

You can also try

SELECT * FROM Customers
WHERE CustomerID=CustomerID; /* query */

or [any_column_name]=[column_name_in_LHL]

(LHL=left hand side.)

copy the query and click here to try code

edited Jan 04 '20 at 11:44

DennisLi

3,915
6
30
66

answered Jan 04 '20 at 10:37

saswat panda

151
1
5

score 2 · Answer 4 · answered Oct 17 '13 at 11:32

2

It would be better to differ the 2 situations and make 2 queries out of it.

If there is no where condition then the DB does not need to evaluate it (potencially faster)
The source code/debugging output is clearer.

answered Oct 17 '13 at 11:32

juergen d

201,996
37
293
362

ya, currently I using two query... but one of my friends has mentioned that, when the program getting larger/ bigger then it is better to find a ways to make one query instead of 2 or more, it's optional... thanks... – 薛源少 Oct 18 '13 at 01:49
You know - this made so much sense to me I completely stopped with this line of thinking and just made one more query. The difference between a few extra lines of code and the nightmares of debugging a conditional query is a no-brainer. – Iofacture Jun 18 '19 at 03:06

score 1 · Answer 5 · answered Oct 17 '13 at 11:24

1

Consider moving the special case inside the query itself, e.g.

SELECT * FROM table_example WHERE '<%=passVar%>' IN ('ALL', First_Col)

answered Oct 17 '13 at 11:24

Dan

4,312
16
28

score 0 · Answer 6 · answered Oct 17 '13 at 11:24

0

Try with wildcard value '%' but I would recommend to use a Factory here to create the SQL statement, what you are trying to do smells a bit.

answered Oct 17 '13 at 11:24

RamonBoza

8,898
6
36
48

score 0 · Answer 7 · edited May 23 '17 at 11:45

0

Something else you could do, is making that combination of code and SQL a single query. Which means the IF..ELSE will be in SQL language.

Check these links for some more info:
MySQL
Using If else in SQL Select statement

edited May 23 '17 at 11:45

Community

1
1

answered Oct 17 '13 at 11:27

lt.kraken

1,287
3
10
27

score -1 · Answer 8 · edited May 23 '23 at 15:25

-1

On sqlserver you can make proc:

create proc select_all_on_null
@a int = null
as
begin
  select * from Records where (@a is null or Record_id=@a )
end

When you select be your program:

make @a in null will select all

if i is number there will select row with this id

edited May 23 '23 at 15:25

Jeevan ebi

105
12

answered May 27 '19 at 21:29

Bahaa Qurini

1
1

This works for parameterized logic, thanks @bahaa – Jeevan ebi Apr 28 '23 at 07:19

Is it possible to use "WHERE" clause to select all records in SQL Statement?

8 Answers8

Linked