EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 600+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials SQL Tutorial SQL WHERE Clause
Secondary Sidebar
SQL Tutorial
  • Clause
    • SQL Clauses
    • SQL IN Operator
    • SQL SELECT DISTINCT Multiple Columns
    • SQL Null Values
    • SQL LIKE
    • SQL LIKE Query
    • SQL LIKE Operator
    • SQL LIKE Clause
    • SQL NOT Operator
    • SQL Minus
    • SQL WHERE Clause
    • SQL with Clause
    • SQL HAVING Clause
    • SQL HAVING Clause
    • SQL GROUP BY DAY
    • ORDER BY Clause in SQL
    • SQL ORDER BY CASE
    • SQL ORDER BY DESC
    • SQL ORDER BY DATE
    • SQL ORDER BY Alphabetical
    • SQL ORDER BY Ascending
    • SQL Order by Count
    • SQL GROUP BY Month
    • SQL GROUP BY Multiple Columns
    • SQL GROUPING SETS
  • Basic
    • What is SQL
    • Careers in SQL
    • Careers in SQL Server
    • IS SQL Microsoft?
    • SQL Management Tools
    • What is SQL Developer
    • Uses of SQL
    • How to Install SQL Server
    • What is SQL Server
    • SQL Quick References
    • SQL Like Wildcard
    • SQL Like with Multiple Values
    • SQL Examples
    • SQL Server Versions
    • SQL DROP DB
    • SQL Case Insensitive
    • SQL Expressions
    • Database in SQL
    • SQL Data Types
    • SQL Keywords
    • Composite Key in SQL
    • SQL WAITFOR
    • SQL Constraints
    • Transactions in SQL
    • First Normal Form
    • SQL Server Data Types
    • SQL Administration
    • SQL Variables
    • SQL Enum
    • SQL GROUP BY WHERE
    • SQL ROW
    • SQL EXECUTE
    • SQL EXCLUDE
    • SQL Performance Tuning
    • SQL UUID
    • Begin SQL
    • SQL Update Join
    • Cheat sheet SQL
  • Operators
    • SQL Operators
    • SQL Arithmetic Operators
    • SQL Logical Operators
    • SQL String Operators
    • Ternary Operator in SQL
  • Commands
    • SQL Commands
    • sqlplus set commands
    • SQL Alter Command
    • SQL Commands Update
    • SQL DML Commands
    • SQL DDL Commands
    • FETCH in SQL
  • Queries
    • SQL Insert Query
    • SQL SELECT Query
    • SQL SELECT RANDOM
    • SQL Except Select
    • SQL Subquery
    • SQL SELECT DISTINCT
    • SQL WITH AS Statement
  • Keys
    • SQL Keys
    • SQL Foreign Key
    • Primary Key in SQL
    • Foreign Key in SQL
    • Unique Key in SQL
    • SQL UNIQUE Constraint
    • SQL Primary Key
    • Alternate Key in SQL
    • SQL Super Key
  • Functions
    • SQL Date Function
    • SQL Server Functions
    • SQL String Functions
    • SQL Compare String
    • Timestamp to Date in SQL
    • SQL REGEX
    • SQL Window Functions
    • SQL Syntax
    • SQL CONCAT
    • SQL ALTER TABLE
    • SQL MOD()
    • SQL Timestamp
    • SQL Min and Max
    • SQL TO_DATE()
    • SQL DATEADD()
    • SQL DATEDIFF()
    • SQL HOUR()
    • SQLite? functions
    • ANY in SQL
    • LIKE Query in SQL
    • SQL NOT NULL
    • SQL NOT IN
    • SQL MAX()
    • SQL MIN()
    • SQL SUM()
    • SQL COUNT
    • SQL identity
    • SQL DELETE Trigger
    • SQL Declare Variable
    • SQL Text Search
    • SQL COUNT DISTINCT
    • SQL TEXT
    • SQL Limit Order By
    • BETWEEN in SQL
    • LTRIM() in SQL
    • TOP in SQL
    • SQL Select Top
    • Merge SQL
    • SQL TRUNCATE()
    • SQL UNION
    • SQL ALL
    • SQL INTERSECT
    • SQL Alias
    • SQL Server Substring
    • CUBE in SQL
    • SQL RANK()
    • SQL CTE
    • SQL LAG()
    • SQL MID
    • SQL avg()
    • SQL WEEK
    • SQL DELETE
    • SQL DATEPART()
    • SQL DECODE()
    • SQL DENSE_RANK()
    • SQL NTILE()
    • SQL NULLIF()
    • SQL Stuff
    • SQL Ceiling
    • SQL EXISTS
    • SQL LEAD()
    • SQL COALESCE
    • SQL BLOB
    • SQL ROW_NUMBER
    • SQL Server Replace
    • SQL Ranking Function
    • SQL Server Permission
  • T-SQL
    • T-SQL pivot
    • T-SQL Formatter
    • T-SQL TRY CATCH
    • T-SQL CTE
    • T-SQL CASE
    • T-SQL DATEPART
    • T-SQL Date Format
    • T-SQL ROUND
    • T-SQL Loop
    • T-SQL IIF
    • T-SQL Union
    • T-SQL CREATE TABLE
    • T-SQL INSERT
    • T-SQL Stuff
    • T-SQL ISNULL
    • T-SQL ADD Column
    • T-SQL DATEDIFF
  • Joins
    • Join Query in SQL
    • Types of Joins in SQL
    • Types of Joins in SQL Server
    • SQL Inner Join
    • SQL Join Two Tables
    • SQL Delete Join
    • SQL Left Join
    • LEFT OUTER JOIN in SQL
    • SQL Right Join
    • SQL Cross Join
    • SQL Outer Join
    • SQL Full Join
    • SQL Self Join
    • Natural Join SQL
    • SQL Multiple Join
  • Advanced
    • MDF File in SQL Server
    • SQL Aliases
    • SQL Hosting
    • SQL Auto Increment
    • SQL Injection
    • SQL Wildcards
    • SQL Check
    • SQL Indexes
    • Select Distinct
    • SQL BETWEEN
    • SQLPlus spool
    • SQL Create Table
    • SQL Schema
    • Comparison Operators in SQL
    • SQL_plus
    • SQL Formatter
    • SQL LEFT INNER JOIN
    • SQL Plus Command
    • SQLPlus not found
    • SQL Injection Attack
    • Aggregate Functions in SQL
    • SQL REVOKE
    • SQL Select Distinct Count
    • IF ELSE Statement in SQL
    • SQL CASE Statement
    • SQL While Loop
    • SQL BIGINT
    • SQL Crosstab
    • SQL Wildcard Character
    • SQL INSTR()
    • SQL now
    • SQL synonyms
    • SQLite?export to csv
    • What is Procedure in SQL
    • Stored Procedure in SQL?
    • SQL Server Constraints
    • SQL DELETE ROW
    • Column in SQL
    • Table in SQL
    • SQL Virtual Table
    • SQL Merge Two Tables
    • SQL Table Partitioning
    • SQL Temporary Table
    • SQL Clone Table
    • SQL Rename Table
    • SQL LOCK TABLE
    • SQL Clear Table
    • SQL DESCRIBE TABLE
    • SQL Mapping
    • Cursors in SQL
    • AND in SQL
    • Wildcard in SQL
    • SQL FETCH NEXT
    • SQL Views
    • SQL Delete View
    • Triggers in SQL
    • SQL UPDATE Trigger
    • SQL AFTER UPDATE Trigger
    • SQL Update Statement
    • SQL DROP TRIGGER
    • SQL DROP Table
    • Types of SQL Views
    • SQL Port
    • SQL Clustered Index
    • SQL COMMIT
    • Distinct Keyword in SQL
    • PARTITION BY in SQL
    • SQL Set Operators
    • SQL UNION ALL
    • Metadata in SQL
    • SQL Bulk Insert
    • Array in SQL
    • SQL REGEXP
    • JSON in SQL
    • SQL For loop
    • EXPLAIN in SQL
    • ROLLUP in SQL
    • Escape Character SQL
    • SQL Cluster
    • SQL Backup
    • SQL Pattern Matching
    • SQL Users
    • ISNULL SQL Server
    • SQL pivot
    • SQL Import CSV
    • SQL if then else
    • SQL ignore-case
    • SQL Matches
    • SQL Search String
    • SQL Column Alias
    • SQL extensions
    • SQL Substring Function
    • Charindex SQL
  • SqlAlchemy
    • What is SQLAlchemy
    • SqlAlchemy ORM
    • SQLAlchemy count
    • SQLAlchemy update object
    • SQLAlchemy pip
    • SQLAlchemy Connection
    • SQLAlchemy Metadata
    • SQLAlchemy Raw SQL
    • SQLAlchemy Filter in List
    • SQLAlchemy Alias
    • SQLAlchemy unique
    • SQLAlchemy JSONB
    • SQLAlchemy Async
    • SQLAlchemy Types
    • SQLAlchemy Many to Many
    • SQLAlchemy Example
    • SQLAlchemy Model
    • SQLAlchemy Data Types
    • SQLAlchemy Filter
    • SQLAlchemy SQLite
    • SQLAlchemy DateTime
    • SQLAlchemy create_engine
    • SQLAlchemy Delete
    • SQLAlchemy Migrations
  • NoSQL
    • NoSQL Databases List
    • NoSQL Data Modeling
    • Types of NoSQL Databases
    • NoSQL Injection
    • NoSQL vs SQL Databases
    • NoSQL Use Cases
    • NoSQL Key Value
  • Interview Questions
    • SQL Interview Questions
    • Advance SQL Interview Questions
    • SQL Joins Interview Questions
    • SQL Server Interview Questions
    • SQL Current Month

Related Courses

JDBC Training Course

PHP course

Windows 10 Training

SQL Course Training

PL/SQL Certification Courses

Oracle Certification Courses

SQL WHERE Clause

SQL WHERE Clause

Introduction to WHERE Clause

As we all know that SQL(Structured Query Language) is one of the most commonly used languages for relational databases (database in which records are saved in the form of rows and columns). In SQL, queries are fired to hit the database to perform desired operations, be it DML (Data Manipulation Language), DDL (Data Definition Language) or DCL (Data Control Language) operations. SQL uses some clauses like WHERE, GROUP BY, HAVING, ORDER BY, which perform specific operations. The WHERE clause is used to apply conditions and filter out results while retrieving or manipulating any data from the database. It is used with the SELECT, UPDATE and DELETE statement also; the WHERE clause is optional to be used with them.

In general, terms, WHERE clause,

  • Used to filter the rows according to the given criteria.
  • Limits the number of rows returned.
  • Followed by a logical condition that returns either true or false.
  • Works only if the mentioned condition returns true.
  • It can be used with SELECT, UPDATE or DELETE statements.

1. Syntax with SELECT

SELECT column1, column2, column3… from table_name WHERE condition;

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

Here SELECT will retrieve all the data of column1, column2, column3 from the table (named as table_name), and the WHERE clause applies the conditions to the data retrieved by SELECT statement and filter that according to the condition mentioned in the statement.

2. Syntax with UPDATE

UPDATE table_name SET column_name = value WHERE condition;

Here Update will update the value for the column_name with provided where condition.

Comparison and Logical Operators can also be used with the WHERE condition like and, or, not, LIKE, <, =, etc.

All in One Software Development Bundle(600+ Courses, 50+ projects)
Python TutorialC SharpJavaJavaScript
C Plus PlusSoftware TestingSQLKali Linux
Price
View Courses
600+ Online Courses | 50+ projects | 3000+ Hours | Verifiable Certificates | Lifetime Access
4.6 (86,697 ratings)

3. Syntax with DELETE

DELETE from table_name WHERE condition;

In the above Syntax :

table_name Name of the table on which operations need to be performed.
condition Specifies the filter condition on which records needs to be filtered
column_list Name of the columns of the table

Examples

Consider a students table having different columns and values mentioned below:

Stu_id Stu_name Stu_address Stu_phno Stu_percentage
1 Rahul Agra 9557806625 85
2 Ankit Delhi 8855664471 75
3 Shailendra Noida 7213457896 92

Scenario #1

Retrieve student id, student name, address, and percentage of all those students who scored more than 80 percent.

Query

SELECT Stu_id, Stu_name, Stu_address from students WHERE Stu_percentage > 80;

Result:

Number of Records: 2

Stu_id Stu_name Stu_address Stu_percentage
1 Rahul Agra 85
3 Shailendra Noida 92

Scenario #2

Update the percentage of Rahul by 2 percent.

Query

UPDATE students SET Stu_percentage = Stu_percentage+2 WHERE Stu_name =‘Rahul’;

Result:

Rows affected: 1

If we hit the query to view the updated fields:

Query

SELECT * from students WHERE Stu_name =’Rahul’;

Result:

Number of Records: 1

Stu_id Stu_name Stu_address Stu_percentage
1 Rahul Agra 87

Scenario #3

Student Ankit has left the school, so delete the whole record of him from the table.

Query

DELETE from students WHERE Stu_name = ’Ankit’;

Result:

Rows affected: 1

To view the updated table, student:

Query

SELECT * from students;

Result:

Rows affected: 2

Stu_id Stu_name Stu_address Stu_phno Stu_percenta ge
1 Rahul Agra 9557806625 87
3 Shailendra Noida 7213457896 92

WHERE clause Operations

The WHERE clause holds the conditions on which database values are filtered. Various operators can be used with the WHERE clause. Some of them are given below in the table with an example:

S.No. Operator Description Example
1. AND Returns true if both the condition matches SELECT * from students WHERE Stu_name =’Rahul’ and Stu_percentage = 85;
2. OR Returns true if either of the

condition matches

SELECT * from students WHERE Stu_name =’Rahul’ or Stu_name = ‘Shalendra’;
3. IN The value matches any of the multiple values specified SELECT * from students WHERE Stu_city IN (‘AGRA’, ‘NOIDA’);
4. NOT IN The value does not match any of the multiple values specified SELECT * from students WHERE Stu_city NOT IN (‘AGRA’, ‘NOIDA’);
5. = Equal SELECT * from students WHERE

Stu_name=’Rahul’;

6. > Greater than SELECT * from students WHERE Stu_percentage > 80;
7. < Less than SELECT * from students WHERE Stu_percentage < 78;
8. >= Greater than or equal to SELECT * from students WHERE Stu_percenetage >=70;
9. <= Less than or equal to SELECT * from students WHERE Stu_percenetage <=70;
10. <> Not equals to SELECT * from students WHERE Stu_percentage <> 75;
11. BETWEEN The value lies between a certain range SELECT * from students WHERE

Stu_percentage BETWEEN 70 AND 85;

12. LIKE Values match a certain pattern. Used to perform wildcard searches SELECT * from students WHERE Stu_city LIKE ‘AG%’;

Note:​ There is one thing that needs to be kept in mind while working with WHERE clause is that while specifying the condition, Numeric values are not quoted in single quotes (‘ ‘) whereas text values (varchar) needs to be quoted in single quotes (‘ ‘).

How does the WHERE Clause work in SQL?

Though the above examples clearly show how WHERE clause is used to filter the data according to the condition specified by the user and allows the condition limits the faster execution of SQL code as the number of records returned. SQL Query optimizer works first on the query using (table on which operation needs to be performed) to retrieve, delete, or update and then apply WHERE clause to the results.

WHERE clause can only be used when we need to filter the results in a single table or join of tables as it works on rows data, but when in case of the Aggregate functions, WHERE cannot be used to apply conditions to the query.

Let’s consider the scenario where the WHERE clause can’t be used:

Scenario: In a ‘movies’ table, retrieve all the details of movies that earn more than 10 crores in particular years like (2000, 2010,2012..etc.)

Query:

SELECT * from movies GROUP BY Year_released HAVING earnings > 10;

The above example uses the HAVING clause instead of WHERE because the WHERE clause cannot be used in the aggregate functions whereas HAVING can, and this is also one of the major differences between WHERE and HAVING clause.

Conclusion – SQL WHERE Clause

The above explanation clearly shows the use of the WHERE clause and its implementation for different scenarios in SQL queries. Before writing any query, one must keep in mind the use of every clause and the scenario in which that particular clause or keyword should be used.

Recommended Articles

This is a guide to SQL WHERE Clause. Here we discuss the use of the WHERE clause and its implementation for different scenarios. You can also go through our other suggested articles –

  1. Types of Joins in SQL
  2. Wildcards in MySQL
  3. SQL Insert Query
  4. Foreign Key in SQL
Popular Course in this category
SQL Training Program (7 Courses, 8+ Projects)
  7 Online Courses |  8 Hands-on Projects |  73+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course

Related Courses

JDBC Training (6 Courses, 7+ Projects)4.9
PHP Training (5 Courses, 3 Project)4.8
Windows 10 Training (4 Courses, 4+ Projects)4.7
PL SQL Training (4 Courses, 2+ Projects)4.7
Oracle Training (14 Courses, 8+ Projects)4.7
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Java Tutorials
  • Python Tutorials
  • All Tutorials
Certification Courses
  • All Courses
  • Software Development Course - All in One Bundle
  • Become a Python Developer
  • Java Course
  • Become a Selenium Automation Tester
  • Become an IoT Developer
  • ASP.NET Course
  • VB.NET Course
  • PHP Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Software Development Course

C# Programming, Conditional Constructs, Loops, Arrays, OOPS Concept

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more