More

    SQL Concepts – Where Clause

    The WHERE clause lets you add further conditions on your query. 

    SELECT * FROM Employees WHERE firstname = ‘Heartin’;

    IN, AND/OR AND BETWEEN AND AND

    Use IN to specify a set of acceptable values in where clause.

    SELECT * FROM mytable WHERE col1 IN(“value1”,”value2”);

    Use AND/OR to combine clauses, and NOT to negate conditions – AND insist all clauses to be true. OR insist any clause to be true.

    SELECT * FROM mytable WHERE col1 NOT IN (“value2”,”value3”);

    You can use BETWEEN and AND to filter results. The range includes endpoints when you use BETWEEN and AND in where clause.

    COMPARISON OPERATORS

    The comparison operators that we can use in a WHERE clause are:

    • = (Equality)
    • <> or != (Not equal to)
    • <=
    • !< (Not less than)
    • >=
    • !>

    SQL don’t do comparisons involving NULL like ‘WHERE id=NULL’. Instead we can use ‘IS NULL’ or ‘IS NOT NULL’.

    USING WILDCARDS

    We can use LIKE to filter queries based on similarity. The wildcards that you can use with LIKE and NOT LIKE are:

    • ‘-’ (Underscore) -> Stands for any one character
    • ‘%’ -> Stands for any one or more characters.

    Below query uses LIKE to find all employees whose first names had 4 characters:

    SELECT * FROM Employees WHERE firstname LIKE ‘_ _ _ _’;

    To search for terms that include the ‘_’ or ‘%’ characters using LIKE, you can escape those characters. You can put any character of your choice before that and tell SQL that character is an escape character using ESCAPE ‘#’ at the end or use backslash to escape.

    SELECT Savings FROM Financials WHERE Savings LIKE ‘10#%’ ESCAPE ‘#’;

    SELECT Savings FROM Financials WHERE Savings LIKE ‘10\%’;

    SIMILAR TO AND MATCH

    The SIMILAR TO clause is similar to LIKE, but we can use regular expressions. Some DBMSs use REGEXP or REGEXP_LIKE instead of SIMILAR TO.

    MATCH with WHERE lets you match individual records.

    … WHERE (‘heartin’,’jacob’) MATCH (SELECT firstname, lastname FROM Employees);

    ALL, ANY, SOME

    The ALL keyword makes SQL checks all results returned from a sub-query. 

    … WHERE id> ALL(SELECT Supervisor FROM Employees)

    ANY means any result. ‘SOME’ means the same as ‘ANY’.

    HAVING CLAUSE

    We can filter groups using HAVING clause when using GROUP BY with WHERE.

    When used together, rule is that the GROUP BY clause must come only after the WHERE clause, but can come before the HAVING clause. So HAVING is required with GROUP BY, when you want to apply some condition after grouping.

    WHERE <condition> GROUP BY <columnName> HAVING condition

    COMMONLY ASKED QUESTIONS BASED ON CONCEPTS IN THIS PAGE:

    1. Give the basic usage of NOT & IN in WHERE clause?
    2. Can we compare null as WHERE id=NULL?
    3. Does range include endpoints when you use BETWEEN and AND in where clause?
    4. What are the wildcards that you can use with LIKE and NOT LIKE? How do you escape if your result can have them?
    5. Give the basic use of HAVING? When is it used? Can we use it along with WHERE?

    Recent Articles

    OAUTH – FREQUENTLY ASKED QUESTIONS FOR INTERVIEWS AND SELF EVALUATION

    Why is refresh token needed when you have access token? Access tokens are usually short-lived and refresh tokens are...

    SUMO LOGIC VIDEOS AND TUTORIALS

    Sumo Logic Basics - Part 1 of 2 (link is external) (Sep 29, 2016)Sumo Logic Basics - Part 2 of 2...

    GIT – USEFUL COMMANDS

    Discard all local changes, but save them for possible re-use later:  git stash Discarding local changes...

    DISTRIBUTED COMPUTING – RECORDED LECTURES (BITS)

    Module 1 - INTRODUCTION Recorded Lecture - 1.1 Introduction Part I – Definition

    BOOK REVIEW GUIDELINES FOR COOKBOOKS

    Whenever you add reviews for the book, please follow below rules. Write issues in an excel.Create an excel...

    Related Stories

    Leave A Reply

    Please enter your comment!
    Please enter your name here

    Stay on op - Ge the daily news in your inbox