SQL WHERE Clause
tags: #sql/basic_statements
Quick Tip!
To execute a query with and without where conditions, include a where 1=1 as a placeholder or a dummy condition as 1=1 is always True.
- Allows you to easily append additional
ANDorORconditions without worrying about addingWHEREorANDat the start of the query. - This helps avoid errors when concatenating conditions.
The WHERE clause is used to filter records.
It is used to extract only those records that fulfill a specified condition.
This condition is often written as a logical statement
SQL comparison and logical operators are primarily used within the WHERE clause of an SQL statement.
Syntax:
SELECT column1, column2, ...
FROM tablename,
WHERE condition;
Special Conditions
- Retrieving records where there is no missing values
...
WHERE column IS NOT NULL
...
- Retrieving records where there is a missing value
...
WHERE column IS NULL
...
// finding missing birthday in employee database
SELECT COUNT(*) AS num_of_missing_bdate
FROM EMPLOYEE
WHERE bdate IS NULL;
What happens if we do not use a WHERE clause?
Without a WHERE clause, the result of the SELECT statement will be the Cartesian product of all the rows from the tables specified in the query. In other words, each row from the first table will be combined with every row from the second table, and so on, resulting in a potentially very large result set.