SQL WHERE - How to Use WHERE and EXISTS Clause in SQL?

Level up your SQL querying skills with our recipe spotlight on SQL WHERE and EXISTS clauses! Elevate your SQL journey with ProjectPro!

Recipe Objective - SQL WHERE - How to Use WHERE and EXISTS Clause in SQL?

Structured Query Language (SQL) is a powerful tool for managing and manipulating relational databases. Among its many features, the WHERE clause stands out as a key element for filtering and retrieving specific data from a database. Check out this recipe to delve into the intricacies of the SQL WHERE clause and explore its advanced counterpart, the EXISTS clause. 

What is WHERE Clause in SQL? 

The WHERE clause in SQL is used to filter records based on specified conditions. It allows you to retrieve only the data that meets specific criteria, thereby improving the efficiency of your queries. Let's explore the syntax and usage of the WHERE clause with examples:

Syntax - 

SELECT column1, column2

FROM table_name

WHERE condition;

WHERE Clause in SQL Example 

SELECT product_name, price

FROM products

WHERE category = 'Electronics';

This query retrieves the names and prices of products in the 'Electronics' category.

How to Use the WHERE Clause in SQL?

Filtering records is done with the WHERE clause. It's used to retrieve data that meet a set of criteria. It is used alongside the SELECT statement.

Syntax:

SELECT column_name1, column_name2

FROM table_name WHERE condition;

Example 1 - Basic WHERE Clause in SQL Server 

Let us filter out data where the country is equal to England from the customers table.

Code:
SELECT * FROM customers WHERE country=”England”;

Output:

+-------------+----------------+------+------------+---------+----------+

| customer_id | customer_name  | age  | city       | country | income   |

+-------------+----------------+------+------------+---------+----------+

|         101 | Thomas Shelby  |   30 | Birmingham | England | 90000000 |

|         103 | Alfie Solomons |   40 | London     | England | 78000000 |

|         105 | May Carleton   |   29 | Sheffield  | England | 78000000 |

+-------------+----------------+------+------------+---------+----------+

3 rows in set (0.03 sec)

Example 2 - SQL WHERE Clause with Condition 

Let us now filter out customer_id where income>60000000

Code:

SELECT customer_id, customer_name FROM customers WHERE income > 60000000;

Output:

+-------------+----------------+

| customer_id | customer_name  |

+-------------+----------------+

|         101 | Thomas Shelby  |

|         103 | Alfie Solomons |

|         105 | May Carleton   |

+-------------+----------------+

3 rows in set (0.00 sec)

Example 3 - SQL WHERE Clause with Multiple Conditions 

The true power of the WHERE clause lies in its ability to handle multiple conditions. You can use logical operators such as AND, OR, and NOT to create complex conditions for precise data retrieval.

SELECT customer_name, order_date

FROM orders

WHERE total_amount > 1000 AND order_date >= '2023-01-01';

This query fetches the names of customers and order dates for orders with a total amount exceeding 1000 and placed after January 1, 2023.

What is the EXIST Clause in SQL? 

The EXISTS clause is a specialized feature that checks for the existence of rows in a subquery.  It returns TRUE if there is a match. It is particularly useful when you want to determine whether a condition is met based on the results of another query. Let's explore the syntax and usage: 

Syntax: 

SELECT column_name1, column_name2, ...

FROM table_name WHERE EXISTS

(SELECT column_name FROM table_name WHERE condition);

How to Use the EXIST Clause in SQL Server? 

Example 1 - EXIST Clause in SQL Example 

Let us see an example. We have created students and teachers tables as follows –

Code:

-- creating table teachers

CREATE TABLE teachers (

    teacher_id numeric PRIMARY KEY,

    teacher_name varchar(80),

    t_subject varchar(80),

    t_salary INTEGER

);

--creating table students

CREATE TABLE students (

    student_id NUMERIC PRIMARY KEY,

    student_name varchar(80),

    teacher_id numeric

);

--  inserting values into teachers table

INSERT INTO teachers
 

VALUES (1, "Miranda", "Mathematics", 50000),

(2,"Aaron","Computers",40000),

(3, "Mary", "Science", 45000),

(4, "John", "English", 40000);

--  inserting values into students table

INSERT INTO students VALUES

(101,"Annie",1),

(102,"Joey",2),

(103,"Sam",3),

(104,"Jammie",2),

(105,"Sean", 3),

(106,"Martha",1),

(107,"Nate",2);

Here is what the data looks like –

The teachers table

+------------+--------------+-------------+----------+

| teacher_id | teacher_name | t_subject   | t_salary |

+------------+--------------+-------------+----------+

|          1 | Miranda      | Mathematics |    50000 |

|          2 | Aaron        | Computers   |    40000 |

|          3 | Mary         | Science     |    45000 |

|          4 | John         | English     |    40000 |

+------------+--------------+-------------+----------+

4 rows in set (0.00 sec)

 

The students table

+------------+--------------+------------+

| student_id | student_name | teacher_id |

+------------+--------------+------------+

|        101 | Annie        |          1 |

|        102 | Joey         |          2 |

|        103 | Sam          |          3 |

|        104 | Jammie       |          2 |

|        105 | Sean         |          3 |

|        106 | Martha       |          1 |

|        107 | Nate         |          2 |

+------------+--------------+------------+

7 rows in set (0.00 sec)

 

Now we will run a query using the exists statement –

Code:

SELECT teacher_name

FROM teachers

WHERE EXISTS

(SELECT teacher_id FROM students WHERE student_id IN (101,102,105,104));

Output:

+--------------+

| teacher_name |

+--------------+

| Miranda      |

| Aaron        |

| Mary         |

| John         |

+--------------+

4 rows in set (0.01 sec)

Explore more about SQL Clauses with ProjectPro!   

Grasping the concepts of SQL WHERE and EXISTS clauses is crucial for effective database management. However, theory alone won't suffice; practical application is paramount. Check out ProjectPro, a comprehensive platform boasting a rich collection of 270+ real-world projects in data science and big data. By immersing yourself in these projects, you not only refine your SQL skills but also gain hands-on experience, bridging the gap between theory and application. Elevate your understanding of SQL clauses and unlock the doors to practical proficiency—explore the world of ProjectPro now!

What Users are saying..

profile image

Savvy Sahai

Data Science Intern, Capgemini
linkedin profile url

As a student looking to break into the field of data engineering and data science, one can get really confused as to which path to take. Very few ways to do it are Google, YouTube, etc. I was one of... Read More

Relevant Projects

End-to-End Speech Emotion Recognition Project using ANN
Speech Emotion Recognition using RAVDESS Audio Dataset - Build an Artificial Neural Network Model to Classify Audio Data into various Emotions like Sad, Happy, Angry, and Neutral

NLP Project for Beginners on Text Processing and Classification
This Project Explains the Basic Text Preprocessing and How to Build a Classification Model in Python

Build a Multi ClassText Classification Model using Naive Bayes
Implement the Naive Bayes Algorithm to build a multi class text classification model in Python.

Build a Credit Default Risk Prediction Model with LightGBM
In this Machine Learning Project, you will build a classification model for default prediction with LightGBM.

Build a Similar Images Finder with Python, Keras, and Tensorflow
Build your own image similarity application using Python to search and find images of products that are similar to any given product. You will implement the K-Nearest Neighbor algorithm to find products with maximum similarity.

Build a Text Classification Model with Attention Mechanism NLP
In this NLP Project, you will learn to build a multi class text classification model with attention mechanism.

Predict Churn for a Telecom company using Logistic Regression
Machine Learning Project in R- Predict the customer churn of telecom sector and find out the key drivers that lead to churn. Learn how the logistic regression model using R can be used to identify the customer churn in telecom dataset.

Medical Image Segmentation Deep Learning Project
In this deep learning project, you will learn to implement Unet++ models for medical image segmentation to detect and classify colorectal polyps.

Demand prediction of driver availability using multistep time series analysis
In this supervised learning machine learning project, you will predict the availability of a driver in a specific area by using multi step time series analysis.

PyTorch Project to Build a GAN Model on MNIST Dataset
In this deep learning project, you will learn how to build a GAN Model on MNIST Dataset for generating new images of handwritten digits.