Diwali Deal : Flat 20% off + 2 free self-paced courses + $200 Voucher  - SCHEDULE CALL

- SQL Server Blogs -

SQL Where Statement Clause with Examples



Introduction

The SQL Server case statement in where clause  or the SQL Where Clause is used to specify a condition while fetching data from a single table or multiple tables are combined together. When a particular condition is satisfied, it returns the desired value from the table. The SQL Where Statement clause should be used to filter records and to fetch only necessary records from the table. The WHERE clause in SQL can be used along with all statements like SELECT, UPDATE, INSERT, etc. Let us understand how to add case statement in where clause in sql?

Read on to know more about SQL Server case in where clause!

Read: SSRS Pie Chart - Having a Slice of the Pie

The basic syntax of Where clause in SQL can be given as:

SELECT column1, column2, columnN'
FROM table_name
WHERE [condition]

You can make the condition more specific by using different comparison or logical operators like equal to, not, etc. Here is a list of comparison operators in SQL.

Operator Description
= Equal
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
<> Not equal
BETWEEN Defines a certain range
LIKE Search for a pattern
IN To specify multiple possible values for a column

Learn SQL Server in the Easiest Way

  • Learn from the videos
  • Learn anytime anywhere
  • Pocket-friendly mode of learning
  • Complimentary eBook available

Why do we need Where Clause in SQL?

The SELECT statement in SQL is used to query data from a database, but it returns all records from the queried database. However, there is a time when we have to restrict the query result to a specified condition. It is when the clause in SQL comes handy. A simple syntax using SELECT and Where clause can be written as:

SELECT * FROM tableName WHERE Condition;

Here, SELECT is the most common statement that is used to fetch the data from the table. The WHERE keyword restricts the query result and condition is the filter applied to the query results. The filter could be a specific range or value or sub-query. Here is one quick example for your reference, how to specific conditions using Where clause in SQL.

Read: SQL Career Path - Step By Step Microsoft SQL Server Career Guide

SELECT * FROM 'members' WHERE 'membership_number' = 1;

Take an example where we want to give personal details of a member whose membership number is one then we can use the given syntax for the same purpose. The objective of Where clause in SQL is:

  • To limit the number of rows using the WHERE clause.
  • It is used to filter rows that meet a specific criterion.
  • Where clause is followed by a particular condition that returns True or False.
  • Where clause can be used with multiple SQL statements like INSERT, SELECT, UPDATE, etc.

SQL Queries Practices

A WHERE clause with SELECT statement looks like

SELECT column-names
FROM table-name
WHERE condition

The SELECT statement in SQL is used to query a database that lists all the records from a table. If you want to restrict your research, then you should combine it with other SQL clauses and WHERE clause is one of the most common examples that restricts the query result based on a specified condition. If you want to work with SQL databases, then it is necessary to have hands-on expertise on SELECT and WHERE clauses otherwise things will not work well for you.

Read: Most Popular SQL Server Performance Tuning Tips

A WHERE clause with UPDATE statement looks like

UPDATE table-name
SET column-name =value
WHERE condition

One of the most popular examples when working with SQL is an Update command that is used to update the existing records within a Table. This is an action query that can be applied to a set of records based on conditions you specify. This is taken as one of the most powerful features of RDBMS because you have the flexibility to modify multiple records together. A depth understanding of the Update Query helps you in improving the performance of a database application instead of performing all changes one by one manually in the code. Also, the maintenance of changes is much easier than you ever think of.

Read: How to Prevent SQL Injection Attacks?

With update command in SQL, you can work on multiple records together and WHERE clause in between to specify some condition. Here, are the possible uses of Update Query within a database –

  • It can be used to set the same values for all the records.
  • A value from another table to your table.
  • A value from the field to the linked table.
  • A value derived from some expression.
  • One VBA function where field values can be used as parameters
  • One customized VBA function where parameters could be set NULL

Read: How Long Does It Take To Learn SQL

A WHERE clause with DELETE statement looks like

DELETE table-name
WHERE Condition

The DELETE statement in SQL is used to delete a table or particular entry from the table based on the specified condition. It will only delete the entries from the table, not the table schema. If you want to delete the entire table from the database along with its structure, then you should use the truncate commands in this sql where case.

Combining SQL clauses with Operators

SQL Server Operators are used to specify conditions within an SQL statement and work as a conjunction to join various conditional statements. You can also use operators in SQL statements. Here is a list of operators that are frequently used by SQL statements:

Read: What is SQL Server Replication and How it Works?

  • Comparison Operators
  • Logical Operators
  • Negate Condition Operators
  • Arithmetic Operators

1). SQL Comparison Operators

There is a list of comparison operators in SQL that are listed below:

  • Greater Than OR “>”
  • Less Than OR “<”
  • Equal to operator OR “=”
  • Not Equal To OR “!=” OR “<>”
  • Greater Than Equal to OR “>=”
  • Less Than Equal to OR “<=”

2). SQL Logical Operators

Following listed logical operators are frequently used by the SQL statements:

  • AND
  • BETWEEN
  • ANY
  • NOT
  • LIKE
  • EXISTS
  • IN
  • OR
  • UNIQUE

Read: Introduction to Stored Procedures and its benefits

3). SQL Arithmetic Operators

For two variables of numeric values, we can use the following arithmetic operators:

Following arithmetic operators are used by the SQL statements:

  1. Addition  “+”
  2. Subtraction “-”
  3. Multiplication “*”
  4. Division “/”
  5. Modulus “%”

With the help of SQL queries, we may choose specific data from bulk data files. It is obvious that we cannot store the whole data in a single data but we should use multiple tables to store data logically. You can combine data from multiple tables to a single table or perform operations on multiple tables as required. It is all possible with the help of SQL operators. Moving ahead, let us see how where clause and operators can be used together.

Register for a demo class now to learn all about SQL clauses in detail with example.

SQL Server Training & Certification

  • No cost for a Demo Class
  • Industry Expert as your Trainer
  • Available as per your schedule
  • Customer Support Available

SQL WHERE clause with AND logical Operator

A WHERE clause in SQL can be used with AND operator, if all filter criteria specified, are met. Consider an example where we want to get the complete list of movies that were released in 2008. You can use the following query for this purpose:

SELECT * FROM 'movies' WHERE 'category_id' = 2 AND 'year_released' = 2008 ;

Here, we can combine two or more conditions to make the query result more specific and accurate. For the above example, it has to meet two conditions, one is category ID and other is movie released time. The other common operator that we can use with WHERE clause is OR logical operator.

Read: SQL BETWEEN: Retrieve Desired Range of Values with Examples

SQL WHERE clause with OR logical Operator

Here is one operator that can be used to WHERE clause in SQL. For AND operator, both conditions should be justified to process the final value. At the same time, when we are using the OR operator, even if one condition meets, the final value is calculated. Here is a quick example for your reference.

SELECT * FROM 'movies' WHERE 'category_id' =1 OR 'category_id' = 2 ;

The following query will process all records either from category 1 or category 2. If we are using AND operator, then the movie name should be given in both the categories. These operators can be combined with queries as per the requirement.

SQL WHERE clause with IN logical Operator

The WHERE clause when combined with the IN operator in SQL, it will fetch results whose values are given within the IN keywords. It makes the result more specific but redundant sometimes. Here is one quick example for your reference.

Read: SQL Server Reporting Service: All You Need to Know about Parameterized Reports

SELECT * FROM 'members' WHERE 'membership_number' IN (1,2,3);

The given query will fetch records whose membership number is either 1,2 or 3. In the same way, you can define category ID or column name as per the requirement.

SQL WHERE clause with NOT IN logical Operator

The WHERE clause when combined with the NOT IN operator in SQL, it will not affect the rows whose values are given within the NOT IN keyword. It makes the result more specific but redundant sometimes. It may create confusion so try using it carefully. Here is one quick example for your reference.

SELECT * FROM 'members' WHERE 'membership_number' NOT in (1,2,3);

The given query will fetch records whose membership number is not 1,2 or 3.

Read: What Is The Difference Between The SQL Inner Join And Outer Joins?

SQL WHERE clause with Comparison Operators

Equal to, less than, not equal to, are a few comparison operators that can be used with the WHERE clause.

EQUAL TO (=)

SELECT * FROM 'members' WHERE 'gender' = 'female';

The given query will fetch all records whose gender is equal to the female. This is a common operator that can be used frequently as per the requirement.

GREATER THAN (>)

Read: How To Use The Exists Operator In The SQL?

Here, you can fetch records whose value is greater than the given value. For example, you want to fetch records where payment paid is greater than 2000. For this purpose, you can use the following query.

SELECT * FROM 'payments' WHERE 'amount_paid' > 2000;

It will quickly show a list of payments that is above 2000 and easy to analyze by anyone. In this way, you can save your time where you don’t have to analyze the whole table, but a few entries can work great for you.

Read: Why Is There Need for Notification In Jenkins?

Summary

  • The SQL Where Clause is used to specify a condition while fetching data from a single table or multiple tables are combined together. A WHERE clause in SQL can be used along with all statements like SELECT, UPDATE, INSERT, etc.
  • SELECT statement in SQL is used to fetch all the records from a table. The UPDATE statement is used to update records within a table. The INSERT statement is used to insert values to a table. A DELETE statement is used to delete records from a table. When they are combined with WHERE clause, it makes the query result more accurate and specific.
  • The WHERE clause can be used in conjunction with logic operators like AND, OR, IN, etc. The WHERE clause can be used in conjunction with comparison operators like Equal to, Not Equal to, Less than, Greater than, etc.

Sign up for online SQL training classes today to master SQL fundamental skills.

SQL Server Training & Certification

  • Personalized Free Consultation
  • Access to Our Learning Management System
  • Access to Our Course Curriculum
  • Be a Part of Our Free Demo Class

Final Words

The blog for SQL where Statement clause gave you a perfect idea of how to use the clause within the query. It tells you about the meaning and necessity of WHERE clause and how to use it in the best way with examples. Also, it explains to you what will happen when the WHERE clause is combined with logical and comparison operators. This is how you can implement the SQL Server case in where clause.

Once you have gone through all the topics, it is the time to practice certain problems and enhance your skill set. For this purpose, you can join the SQL certification course with JanBask Training and get certified with us. We help you in becoming a valuable IT resource for whom getting jobs with attractive salary packages is easier. All the Best!

Read: What is Data Mining SQL? Data Mining SQL Tutorial Guide for Beginner

fbicons FaceBook twitterTwitter lingedinLinkedIn pinterest Pinterest emailEmail

     Logo

    JanBask Training

    A dynamic, highly professional, and a global online training course provider committed to propelling the next generation of technology learners with a whole new way of training experience.


  • fb-15
  • twitter-15
  • linkedin-15

Comments

Trending Courses

Cyber Security Course

Cyber Security

  • Introduction to cybersecurity
  • Cryptography and Secure Communication 
  • Cloud Computing Architectural Framework
  • Security Architectures and Models
Cyber Security Course

Upcoming Class

18 days 23 Nov 2024

QA Course

QA

  • Introduction and Software Testing
  • Software Test Life Cycle
  • Automation Testing and API Testing
  • Selenium framework development using Testing
QA Course

Upcoming Class

7 days 12 Nov 2024

Salesforce Course

Salesforce

  • Salesforce Configuration Introduction
  • Security & Automation Process
  • Sales & Service Cloud
  • Apex Programming, SOQL & SOSL
Salesforce Course

Upcoming Class

6 days 11 Nov 2024

Business Analyst Course

Business Analyst

  • BA & Stakeholders Overview
  • BPMN, Requirement Elicitation
  • BA Tools & Design Documents
  • Enterprise Analysis, Agile & Scrum
Business Analyst Course

Upcoming Class

-1 day 04 Nov 2024

MS SQL Server Course

MS SQL Server

  • Introduction & Database Query
  • Programming, Indexes & System Functions
  • SSIS Package Development Procedures
  • SSRS Report Design
MS SQL Server Course

Upcoming Class

10 days 15 Nov 2024

Data Science Course

Data Science

  • Data Science Introduction
  • Hadoop and Spark Overview
  • Python & Intro to R Programming
  • Machine Learning
Data Science Course

Upcoming Class

3 days 08 Nov 2024

DevOps Course

DevOps

  • Intro to DevOps
  • GIT and Maven
  • Jenkins & Ansible
  • Docker and Cloud Computing
DevOps Course

Upcoming Class

2 days 07 Nov 2024

Hadoop Course

Hadoop

  • Architecture, HDFS & MapReduce
  • Unix Shell & Apache Pig Installation
  • HIVE Installation & User-Defined Functions
  • SQOOP & Hbase Installation
Hadoop Course

Upcoming Class

3 days 08 Nov 2024

Python Course

Python

  • Features of Python
  • Python Editors and IDEs
  • Data types and Variables
  • Python File Operation
Python Course

Upcoming Class

4 days 09 Nov 2024

Artificial Intelligence Course

Artificial Intelligence

  • Components of AI
  • Categories of Machine Learning
  • Recurrent Neural Networks
  • Recurrent Neural Networks
Artificial Intelligence Course

Upcoming Class

3 days 08 Nov 2024

Machine Learning Course

Machine Learning

  • Introduction to Machine Learning & Python
  • Machine Learning: Supervised Learning
  • Machine Learning: Unsupervised Learning
Machine Learning Course

Upcoming Class

10 days 15 Nov 2024

 Tableau Course

Tableau

  • Introduction to Tableau Desktop
  • Data Transformation Methods
  • Configuring tableau server
  • Integration with R & Hadoop
 Tableau Course

Upcoming Class

3 days 08 Nov 2024

Search Posts

Reset

Receive Latest Materials and Offers on SQL Server Course

Interviews