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 a WHERE clause

• It appears right after FROM clause.

• The condition must evaluate to true, false or unkown. It can be used with boolean expressions like AND and OR operators

• It order of evalutation is as follows

  • 
• NOTE: You can't use COLUMN ALIAS with WHERE clause

Comparison Operators

Example 1 (Using WHERE with IN operator)

SELECT
	first_name,
	last_name
FROM
	customer
WHERE
	first_name IN ('Noman','Ali');

Example 2 (Using WHERE with LIKE operator)

SELECT
	first_name,
	last_name
FROM
	customer
WHERE
	first_name LIKE 'Al%';

The % is called a wildcard that matches any string

Example 3 (Using WHERE with BETWEEN operator)

SELECT
	first_name,
	LENGTH(first_name) name_length
FROM
	customer
WHERE
	first_name LIKE 'A%' AND
	LENGTH(first_name) BETWEEN 3 AND 5
ORDER BY
	name_length;

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 query

• If we want to skip number of rows before returning the limited rows, we can use the OFFSET clause placed after the LIMIT clause

Example 1

SELECT
	film_id
FROM
	film
ORDER BY
	film_id
LIMIT 5;

Example 2

SELECT
	film_id
FROM
	film
ORDER BY
	film_id
LIMIT 4 OFFSET 3;

Fetch

(limit the number of rows returned by a query)

• LIMIT clause is not a SQL-standard, FETCH is a SQL standard

• If 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

  • Copy

     OFFSET start { ROW | ROWS }
     FETCH { FIRST | NEXT } [ row_count ] { ROW | ROWS } ONLY

    • ROW is the synonym for ROWS, FIRST is the synonym for NEXT. So you can use them interchangeably

    • The start is an integer that must be zero or positive. By default, it is zero if the OFFSET clause is not specified. In case the start 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

SELECT
	film_id,
	title
FROM
	film
ORDER BY
	title
FETCH FIRST 5 ROW ONLY;

Example 2

SELECT
	film_id,
	title
FROM
	film
ORDER BY
	title
OFFSET 5 ROWS
FETCH FIRST ROW ONLY;

In

(select data that matches any value in a list of values)

• You use IN operator in the WHERE 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 this

  • value IN (SELECT column_name FROM table_name);

Example 1

SELECT customer_id,
	rental_id,
	return_date
FROM
	rental
WHERE
	customer_id IN (1, 2)
ORDER BY
	return_date DESC;

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

SELECT customer_id
FROM rental
WHERE CASE (return_date AS DATE) = '2005-05-27'
ORDER BY customer_id;

Because, it returns a list of values, we can use it in input of IN operator

SELECT
	customer_id,
	first_name,
	last_name
FROM
	customer
WHERE
	customer_id IN (
		SELECT customer_id
		FROM rental
		WHERE CASE (return_date AS DATE) = '2005-05-27'
	)
ORDER BY customer_id;

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

SELECT
	customer_id,
	payment_id,
	amount,
	payment_date
FROM
	payment
WHERE
	payment_date BETWEEN '2007-02-07' AND '2007-02-15';

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 and NOT ILIKE operator as shown below:

  • 

Example 1

SELECT
	'foo' LIKE 'foo', -- true
	'foo' LIKE 'f%', -- true
	'foo' LIKE '_o_', -- true
	'bar' LIKE 'b_'; -- false

Example 2

SELECT
	first_name,
	last_name
FROM
	customer
WHERE
	first_name ILIKE 'BAR%';

Is Null

(check if a value is null or not)

• Used for NULL comparison

Example 1 (Find all contacts whose phone number is NULL)

SELECT
    id,
    first_name,
    last_name,
    email,
    phone
FROM
    contacts
WHERE
    phone = NULL;