CODEWITHSUNDEEP
javamysqlsqlsql-server
薛源少
薛源少

Reputation: 306

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

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

Upvotes: 5

Views: 31588

Answers (8)

Bahaa Qurini
Bahaa Qurini

Reputation: 27

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

Upvotes: -1

saswat panda
saswat panda

Reputation: 301

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

Upvotes: 3

juergen d
juergen d

Reputation: 204894

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.

Upvotes: 2

lt.kraken
lt.kraken

Reputation: 1300

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

Upvotes: 0

Pradeep Simha
Pradeep Simha

Reputation: 18133

This also works:

WHERE columnname LIKE '%'

Except for NULL values.

Upvotes: 4

Dan
Dan

Reputation: 4522

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

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

Upvotes: 1

RamonBoza
RamonBoza

Reputation: 9038

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.

Upvotes: 0

Sashi Kant
Sashi Kant

Reputation: 13465

Try with WHERE 1=1::

Select * from myTable  WHERE 1=1

Upvotes: 15

Related Questions