Tutorialsteacher

Follow Us

Articles
  • C#
  • C# OOP
  • ASP.NET Core
  • ASP.NET MVC
  • LINQ
  • Inversion of Control (IoC)
  • Web API
  • JavaScript
  • TypeScript
  • jQuery
  • Angular 11
  • Node.js
  • D3.js
  • Sass
  • Python
  • Go lang
  • HTTPS (SSL)
  • Regex
  • SQL
  • SQL Server
  • PostgreSQL
  • MongoDB
  • SQL - Getting Started
  • What is SQL
  • Create Table
  • ALTER TABLE Statements
  • Rename Columns
  • Modify Column Type
  • Drop Columns
  • Rename Tables
  • Drop Tables
  • Insert Statement
  • Update Statement
  • Delete Statement
  • Truncate Statement
  • Merge Statement
  • Null Value
  • Select Query
  • WHERE Clause
  • GROUP BY Clause
  • HAVING Clause
  • ORDER BY Clause
  • SQL - Inner Join
  • SQL - Left Join
  • SQL - Right Join
  • SQL - Full Join
  • SQL - BETWEEN
  • SQL - IN
  • SQL - LIKE
  • SQL - INTERSECT
  • SQL - MINUS
  • SQL - UNION
  • SQL - UNION ALL
  • SQL - DISTINCT
  • SQL - ANY, SOME
  • SQL - ALL
  • SQL - AVG()
  • SQL - COUNT()
  • SQL - MAX()
  • SQL - MIN()
  • SQL - SUM()
Entity Framework Extensions - Boost EF Core 9
  Bulk Insert
  Bulk Delete
  Bulk Update
  Bulk Merge

SQL - WHERE Clause

The SELECT query can also have an optional WHERE clause to filter the data. The WHERE clause can include one or more boolean conditions to filter out data of the tables.

The WHERE clause always comes after the FROM clause and before GROUP BY, HAVING, and ORDER BY clauses.

Syntax:

SELECT column1, column2,...columnN FROM table_name WHERE conditions

For the demo purpose, we will use the following Employee and Department tables in all examples.

Employee Table
EmpIdFirstNameLastNameEmailPhoneNoSalaryDeptId
1'John''King''[email protected]''650.127.1834'330001
2'James''Bond'2
3'Neena''Kochhar''[email protected]''123.456.4568'170002
4'Lex''De Haan''[email protected]''123.456.4569'150001
Department Table
DeptIdName
1'Finance'
2'HR'

The WHERE clause can contain one or more conditions that can use conditional operators to filter out the result data. Consider the following query.

SQL Script: SELECT Query with WHERE Clause
SELECT * FROM Employee 
WHERE Salary > 16000;

In the above query, the condition Salary > 16000 returns rows where the value in the Salary column is greater than 16000. The above query will return the following result.

EmpIdFirstNameLastNameEmailPhoneNoSalaryDeptId
1'John''King''[email protected]''650.127.1834'330001
3'Neena''Kochhar''[email protected]''123.456.4568'170002

The following query uses the BETWEEN operator in the WHERE clause.

SQL Script: BETWEEN Operator in WHERE Clause
SELECT * FROM Employee 
WHERE Salary BETWEEN 15000 AND 20000;

In the above query, the condition Salary BETWEEN 15000 AND 20000 returns rows where the value in the Salary column is between 15000 and 20000 (including both values). The above query will get the following records.

EmpIdFirstNameLastNameEmailPhoneNoSalaryDeptId
3'Neena''Kochhar''[email protected]''123.456.4568'170002
4'Lex''De Haan''[email protected]''123.456.4569'150001

Multiple Conditions in WHERE Clause

The WHERE clause can contain multiple conditions using AND and OR operators. The following query uses logical operator AND to specify two conditions to filter out data.

SQL Script: Multiple Conditions in WHERE Clause
SELECT * FROM Employee 
WHERE DeptId = 1 AND Salary > 20000;

In the above query, the WHERE condition DeptId = 1 AND Salary > 20000 specifies two conditions separated by AND operator. This will return rows from the Employee table where the value of DeptId is 1 and Salary is greater than 20000.

EmpIdFirstNameLastNameEmailPhoneNoSalaryDeptId
1'John''King''[email protected]''650.127.1834'330001

Subquery in WHERE Clause

The WHERE condition can also use the resulted value of a subquery, as shown below.

SQL Script: Subquery in WHERE Clause
SELECT * FROM Employee 
WHERE DeptId = (SELECT DeptId FROM Department WHERE Name = "HR");

In the above query, the WHERE condition is WHERE DeptId = (SELECT DeptId FROM Department WHERE Name = "HR"). So, first, the subquery SELECT DeptId FROM Department WHERE Name = "HR" would be executed, and the resulted DeptId value will be used to filter out the rows.

EmpIdFirstNameLastNameEmailPhoneNoSalaryDeptId
2'James''Bond'2
3'Neena''Kochhar''[email protected]''123.456.4568'170002

Conditional Operators

The following operators can be used in the WHERE conditions.

OperatorDescription
=Equal
>Greater than
<Less than
>=Greater than or equal
<=Less than or equal
<> or !=Not equal. In some databases, the != is used to compare values which are not equal.
BETWEENBetween some range
LIKESearch for a pattern
INTo specify multiple possible values for a column
TUTORIALSTEACHER.COM

TutorialsTeacher.com is your authoritative source for comprehensive technologies tutorials, tailored to guide you through mastering various web and other technologies through a step-by-step approach.

Our content helps you to learn technologies easily and quickly for learners of all levels. By accessing this platform, you acknowledge that you have reviewed and consented to abide by our Terms of Use and Privacy Policy, designed to safeguard your experience and privacy rights.

[email protected]

ABOUT USTERMS OF USEPRIVACY POLICY
copywrite-symbol

2024 TutorialsTeacher.com. (v 1.2) All Rights Reserved.