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

Ameeruddin Mohammed

ETL (Abintio) developer at IBM
linkedin profile url

I come from a background in Marketing and Analytics and when I developed an interest in Machine Learning algorithms, I did multiple in-class courses from reputed institutions though I got good... Read More

Relevant Projects

Hands-On Approach to Master PyTorch Tensors with Examples
In this deep learning project, you will learn how to perform various operations on the building block of PyTorch : Tensors.

Image Classification Model using Transfer Learning in PyTorch
In this PyTorch Project, you will build an image classification model in PyTorch using the ResNet pre-trained model.

Deep Learning Project for Beginners with Source Code Part 1
Learn to implement deep neural networks in Python .

Ecommerce product reviews - Pairwise ranking and sentiment analysis
This project analyzes a dataset containing ecommerce product reviews. The goal is to use machine learning models to perform sentiment analysis on product reviews and rank them based on relevance. Reviews play a key role in product recommendation systems.

Time Series Forecasting Project-Building ARIMA Model in Python
Build a time series ARIMA model in Python to forecast the use of arrival rate density to support staffing decisions at call centres.

MLOps Project on GCP using Kubeflow for Model Deployment
MLOps using Kubeflow on GCP - Build and deploy a deep learning model on Google Cloud Platform using Kubeflow pipelines in Python

Create Your First Chatbot with RASA NLU Model and Python
Learn the basic aspects of chatbot development and open source conversational AI RASA to create a simple AI powered chatbot on your own.

OpenCV Project for Beginners to Learn Computer Vision Basics
In this OpenCV project, you will learn computer vision basics and the fundamentals of OpenCV library using Python.

Learn Hyperparameter Tuning for Neural Networks with PyTorch
In this Deep Learning Project, you will learn how to optimally tune the hyperparameters (learning rate, epochs, dropout, early stopping) of a neural network model in PyTorch to improve model performance.

Build a Face Recognition System in Python using FaceNet
In this deep learning project, you will build your own face recognition system in Python using OpenCV and FaceNet by extracting features from an image of a person's face.