Skip to content

Filtering Data

Where clause

SELECT select_list
FROM table_name
WHERE condition
ORDER BY sort_expression

  • Besides the SELECT statement, you can use the WHERE clause in the UPDATE and DELETE statement to specify rows to be updated or deleted.

  • To form the condition in the WHERE clause, you use comparison and logical operators: ``` = Equal

          Greater than

    < Less than = Greater than or equal <= Less than or equal <> or != Not equal AND Logical operator AND OR Logical operator OR IN Return true if a value matches any value in a list BETWEEN Return true if a value is between a range of values LIKE Return true if a value matches a pattern IS NULL Return true if a value is NULL IS NOT NULL Returns true if a value is not null NOT Negate the result of other operators ```

Examples

select * from product where id in (1,2,3,4) AND price between 5000 AND 11000;

Select * from product where id >= 3 AND NOT(country is null);

select * from product where id is not null;

Select * from product where country <> 'CHINA' AND name Like '%a%';

Limit Clause

  • PostgreSQL LIMIT is an optional clause of the SELECT statement that constrains the number of rows returned by the query.
SELECT select_list
FROM table_name
LIMIT row_count OFFSET row_to_skip;

Example

select * from product order by id Limit 2 offset 3;

 id |                        name                        | price |          country
----+----------------------------------------------------+-------+---------------------------
  4 | CG                                                 |   500 | NEPAL
  5 | Tesla                                              | 11500 | USA

Fetch Clause

  • To constrain the number of rows returned by a query, you often use the LIMIT clause.
  • The LIMIT clause is widely used by many relational database management systems such as MySQL, H2, and HSQLDB.
  • However, the LIMIT clause is not a SQL-standard.
  • To conform with the SQL standard, PostgreSQL supports the FETCH clause to retrieve a number of rows returned by a query.
OFFSET start { ROW | ROWS }
FETCH { FIRST | NEXT } [ row_count ] { ROW | ROWS } ONLY

Examples:

select * from product order by id
OFFSET 5 ROWS
FETCH FIRST 5 ROWS ONLY;