SQL Where Clause

on May 22, 2021
🔥 1042 views
Contents hide
1) SQL WHERE Clause
1.1) Syntax
1.2) Query
2) Operators in SQL WHERE Clause
2.1) Greater than ( > )
2.2) Less than ( < )
2.3) Less than or equal ( >= )
2.4) Greater than or equal ( <= )
2.5) Not equal ( <> or != )
2.6) Certain range ( BETWEEN )
2.7) Pattern search ( LIKE )
2.8) Multiple possible values ( IN )

The SQL WHERE Clause is used to filter out the records. This Query allows you to add a condition that will return a resultset with that matching condition.

SQL WHERE Clause

You can use the WHERE clause not only in SELECT statements but also you can use in UPDATE, DELETE, etc.

Syntax


  SELECT column_1, column_2 
  FROM table_name
  WHERE condition;

Query

Consider a customer table having multiples records

id customerNumber customerName sales country
1 C-01 Steve 34223 USA
2 C-02 John 45232 USA
3 C-03 Yaya Vanakova 12000 Russia

consider we want to select all the customers from the country “USA”, in the “Customers” table:


    -- Filtering on Text field
    SELECT * FROM customers WHERE country = 'USA';

    -- returns 2 records having country as `USA`

consider we want to select a customer whose ID is 2, in the “Customers” table:


    -- Filtering on Numeric field
    SELECT * FROM customers WHERE id = 2;

    -- returns 1 records having id `2`

Operators in SQL WHERE Clause

Other than equals to ( = ) we can use some more operators which are listed below:

Greater than ( > )

Checks if the value is greater, than its return in result set.

SELECT * FROM customer WHERE sales > 20000;

Less than ( < )

Checks if the value is lesser, than its return in result set.

SELECT * FROM customer WHERE sales < 20000;

Less than or equal ( >= )

Checks if the value is greater or equal, than its return in result set.

SELECT * FROM customer WHERE sales >= 12000;

Greater than or equal ( <= )

Checks if the value is lesser or equal, than its return in result set.

SELECT * FROM customer WHERE sales <= 12000;

Not equal ( <> or != )

Return all the records which are not equal to the define value.

Note: In some versions of SQL this operator may be written as !=
 
SELECT * FROM customer WHERE sales <> 12000;
-- or
SELECT * FROM customer WHERE sales != 12000;

Certain range ( BETWEEN )

Return all the records which are between a certain range

SELECT * FROM customer WHERE sales BETWEEN 10000 AND 20000;

Pattern search ( LIKE )

Return all the records which are matching, starting, or ending with a pattern.

SELECT * FROM customer WHERE customerName LIKE 'Yaya%';
Note: We have a separate lesson for searching a pattern i.e. SQL Wildcards lesson.

Multiple possible values ( IN )

In where clause you can assign multiple value for a single field using IN.

SELECT * FROM customer WHERE country IN ('USA', 'Russia');