2. Filtering Data
Where
(filter rows based on a specified condition)
SELECT
statement returns all rows from one or more columns in a table. To select rows that satisfy a specified condition, we use aWHERE
clauseIt appears right after
FROM
clause.The condition must evaluate to true, false or unkown. It can be used with boolean expressions like
AND
andOR
operatorsIt order of evalutation is as follows
NOTE: You can't use COLUMN ALIAS with WHERE clause
Comparison Operators
Operator | Description |
---|---|
| Equal |
| Greater than |
| Less than |
| Greater than or equal |
| Less than or equal |
| Not equal |
| logitcal operator AND |
| logical operator OR |
| Return true if a value matches any value in a list |
| Retruns true if a value is between a range of values |
| Returns true if a value matches a pattern |
| Returns true if a value is NULL |
| Negate the result of other operators |
Example 1 (Using WHERE with IN operator)
Example 2 (Using WHERE with LIKE operator)
The % is called a wildcard that matches any string
Example 3 (Using WHERE with BETWEEN operator)
Limit
(get a subset of rows generated by a query)
It is the optional clause of the
SELECT
statement that contraints no of rows returned by the queryIf we want to skip number of rows before returning the limited rows, we can use the
OFFSET
clause placed after theLIMIT
clause
Example 1
Example 2
Fetch
(limit the number of rows returned by a query)
LIMIT
clause is not a SQL-standard,FETCH
is a SQL standardIf you plan to make your application compatible with other database systems, you should use the
FETCH
clause because it follows the standard SQL.Its syntax is like this
ROW
is the synonym forROWS
,FIRST
is the synonym forNEXT
. So you can use them interchangeablyThe
start
is an integer that must be zero or positive. By default, it is zero if theOFFSET
clause is not specified. In case thestart
is greater than the number of rows in the result set, no rows are returned;The
row_count
is 1 or greater. By default, the default value of row_count is 1 if you do not specify it explicitly.
Example 1
Example 2
In
(select data that matches any value in a list of values)
You use
IN
operator in theWHERE
clause to check if a value matches any value in a list of values.The list of values can be list of literal values such as numbers, strings or a result or
SELECT
statement like thisvalue IN (SELECT column_name FROM table_name);
Example 1
Example 2 (Get Customers information from customer table whose return date from rental table is '2005-05-27')
The following query returns a list of customer ids from the rental table with the return date is 2005-05-27
Because, it returns a list of values, we can use it in input of IN
operator
Between
(select data that is a range of values)
You use the BETWEEN operator to match a value against a range of values. The following illustrates the syntax of the BETWEEN operator:
value BETWEEN low AND high;
Example 1
Like
(filter data based on pattern matching)
It is used for string pattern matching
Percent sign
%
matches any sequence of zero or more characters.Underscore sign
_
matches any single character.PostgreSQL supports the
ILIKE
operator that works like the LIKE operator. In addition, the ILIKE operator matches value case-insensitively.PostgreSQL also provides some operators that act like the
LIKE
,NOT LIKE
,ILIKE
andNOT ILIKE
operator as shown below:
Example 1
Example 2
Is Null
(check if a value is null or not)
Used for NULL comparison
Example 1 (Find all contacts whose phone number is NULL)
Last updated