The WHERE clause in PostgreSQL is used to filter rows based on a specified condition. It returns only the records that satisfy the condition, making it easier to retrieve specific data from a table.
- Can be used with SELECT, UPDATE and DELETE statements.
- Supports comparison, logical and special operators.
- Allows multiple conditions using AND, OR and NOT.
Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Where:
- column1, column2, ... : The columns to retrieve.
- table_name: The table from which data is retrieved.
- condition: The condition used to filter rows.
Working
First, create the following movie table, which will be used in the examples below.
Example 1: Using WHERE with a Comparison Operator
The following query retrieves movies with a rating greater than 8.5.
SELECT movie_title, rating
FROM movies
WHERE rating > 8.5;
Output:
Example 2: Using WHERE with a String Condition
The following query retrieves movies from the Sci-Fi genre.
SELECT *
FROM movies
WHERE genre = 'Sci-Fi';
Output:
Example 3: Using WHERE with Multiple Conditions
The following query retrieves Sci-Fi movies with a rating greater than 8.7.
SELECT movie_title, rating
FROM movies
WHERE Genre = 'Sci-Fi'
AND rating > 8.7;
Output:
Example 4: Using WHERE with UPDATE
The following query updates the rating of the movie Inception to 9.0.
UPDATE movies
SET rating = 9.0
WHERE movie_title = 'Inception';
Output:
- The UPDATE statement modifies existing records in the movies table.
- The WHERE clause ensures that only the row where movie_title is 'Inception' is updated.
- Without the WHERE clause, the rating movie value of all movies would be updated.
