SQL Query to Select Data from Tables Using Join and Where
Last Updated : 02 Dec, 2024
In SQL, the JOIN clause combines data from multiple tables based on a common column, while the WHERE clause filters the results based on specific conditions. Together, they allow us to retrieve relevant data efficiently from related tables. This article will guide us in using SQL JOIN and WHERE clauses to efficiently select and filter data, with practical examples to help us master these essential SQL techniques.
What is a Query in SQL?
A SQL query is a request to retrieve or manipulate data from a database. SQL queries help developers and data analysts to interact with data stored in relational database systems, such as MySQL. Queries can be used for a variety of purposes, including:
- Retrieving data for analysis or reporting.
- Updating records in the database.
- Deleting or modifying data based on certain conditions.
- Performing calculations or aggregations on data.
What is Join in SQL?
A JOIN in SQL is a command that allows you to combine rows from two or more tables based on a related column between them. SQL JOINs are essential for querying multiple tables that are related by a common key or column.
What is the Where Clause in SQL?
The WHERE clause is used to filter records in SQL queries based on specified conditions. It helps to limit the results based on criteria such as numeric ranges, text patterns, or specific field values.
- Retrieve records where employees' salaries are greater than 6,00,000.
- Filter data to only return customers from a specific city or those who made purchases within a certain date range.
Step-by-Step Guide to SQL Join and WHERE Clause Operations
To begin using SQL JOIN and WHERE clauses to select data from multiple tables, we first need to set up a database with some sample tables. Below is a structured approach to help you get started with these operations.
Step 1: Create a Database
Start by creating a new database where you will store your tables:
CREATE DATABASE geeksforgeeks;
Step 2: Select the Database
Once the database is created, enter it to start using it:
USE geeksforgeeks;
Step 3: Create the employee Table
Create a table1 as employee in the database where we will perform our operations -
CREATE TABLE employee ( ID int(10),
Name varchar(55),
Email varchar(100),
Department int(10)
);
Step 4: Create the dept Table
Create another table2 as dept where we will store the data of employees of the second company-
CREATE TABLE dept ( ID int(10),
Name varchar(55),
hodId int(10),
profit int(20)
);
Step 5: View Table Schemas
View the schema of the table to ensure the table is correct -
> DESC employee;
> DESC dept;
Step 6: Insert Data into the employee Table
Insert sample employee data into the employee table:
INSERT INTO employee VALUES(1, "Devesh", "[email protected]", 1);
INSERT INTO employee VALUES(2, "Mayank", "[email protected]", 1);
INSERT INTO employee VALUES(3, "Aditya", "[email protected]", 2);
INSERT INTO employee VALUES(4, "Divyanshi", "[email protected]", 2);
INSERT INTO employee VALUES(5, "Megha", "[email protected]", 3);
INSERT INTO employee VALUES(6, "Himanshi", "[email protected]", 3);
INSERT INTO employee VALUES(7, "Tanishka", "[email protected]", 4);
INSERT INTO employee VALUES(8, "Jatin", "[email protected]", 4);
Step 7: Insert Data into the dept Table
Now, insert data into the dept table:
INSERT INTO dept VALUES(1, "Computer Science", 1, 100000);
INSERT INTO dept VALUES(2, "Electrical", 2, 45000);
INSERT INTO dept VALUES(3, "Biotechnology", 3, 30000);
INSERT INTO dept VALUES(4, "Architecture", 4, 15000);
Step 8: Query the Data Using JOIN and WHERE Clauses
Now, let's perform some queries using JOIN and WHERE clauses to filter and combine data from the two tables.
Example 1: Retrieve Employees Who Are HODs of Departments
This query joins the employee and dept tables and retrieves employees whose IDs match the hodId of the departments.
Query:
SELECT employee.ID, employee.Name, employee.Email
FROM employee
JOIN dept
WHERE
employee.ID = dept.hodId;
Output
Example 2: Retrieve Employees from Departments with Profit Greater Than 45,000
Here we perform a LEFT JOIN to get all employees from departments where the profit is greater than 45,000.
Query:
SELECT *
FROM employee
LEFT JOIN dept
ON
employee.Department = dept.ID
WHERE
employee.Name IN
(SELECT Name FROM employee WHERE dept.profit > 45000);
Output
Example 3: Retrieve All Data Using FULL JOIN (Cross Join)
A FULL JOIN retrieves all records from both the employee and dept tables. Shows all records from both tables, even if there’s no match in one of them.
Query:
SELECT *
FROM employee
FULL JOIN dept
WHERE
dept.id > 0;
Output
Example 4: Retrieve Employees from Departments with Profit Greater Than 5,000
This query shows employees from departments whose total profit is greater than 5,000. This query will Display department IDs, names, and HOD IDs for departments with profits greater than 5,000.
Query:
SELECT DISTINCT dept.ID, dept.Name, dept.hodId
FROM dept
JOIN employee
ON
dept.ID = employee.Department
WHERE
hodId IN
(SELECT hodId FROM dept WHERE hodId > 0);
Output
SQL Select Using Join and Where Clause Example4Conclusion
By understanding how to use SQL JOINs and the WHERE clause, we can easily combine data from multiple tables based on specific conditions. Optimizing SQL queries ensures faster and more accurate results. Remember, different types of JOINs allow us to retrieve various relationships between tables, and the WHERE clause enables filtering of data for precise results.
Similar Reads
SELECT Data from Multiple Tables in SQL
In SQL (Structured Query Language), it is a common requirement to retrieve data from more than one table at once. When you work with relational databases, you often have to combine data from multiple tables to get meaningful results. SQL provides many methods for selecting data from multiple tables,
4 min read
How to Retrieve Data From Multiple Tables Using PL/SQL Cursors
In database programming, the ability to retrieve data from multiple tables is essential for building robust and efficient applications. PL/SQL Cursors is a powerful feature that enables developers to navigate through result sets and make them the best option for querying data from multiple tables. I
4 min read
How to Use SQL DISTINCT and TOP in Same Query?
Structured Query Language (SQL) is a computer language used to interact with relational databases. It allows us to organize, manage, and retrieve data efficiently. In this article, we will explain how to use the DISTINCT keyword and the TOP clause together in a query, explaining their purpose, usage
4 min read
How to Retrieve Data from Multiple Tables in SQL?
In SQL, retrieving data from multiple tables is a common requirement in database operations. Efficiently combining data from different tables allows developers to create complex queries and extract valuable insights from interconnected datasets. In this article, we will explore multiple approaches t
5 min read
How to Retrieve Data from Multiple Tables in PL/SQL
PL/SQL is “Procedural Language extensions to the Structured Query Languageâ€. SQL is a popular language for both querying and updating data in relational database management systems (RDBMS). PL/SQL adds many procedural constructs to SQL language to overcome some limitations of SQL. In addition, PL/SQ
5 min read
SQL Query to Filter a Table using Another Table
In this article, we will see, how to filter a table using another table. We can perform the function by using a subquery in place of the condition in WHERE Clause. A query inside another query is called subquery. It can also be called a nested query. One SQL code can have one or more than one nested
2 min read
SQL Query to Select all Records From Employee Table Where Name is Not Specified
In SQL, filtering records based on specific criteria is a fundamental task when working with relational databases. One common scenario is selecting all records from a table while excluding certain values, such as specific names or designations. This article demonstrates how to use SQL queries effect
4 min read
How to Query Two Tables For Duplicate Values in SQL?
When working with relational databases, it's common to identify duplicate values across multiple tables. SQL provides efficient ways to query such data using different techniques. These methods help streamline data analysis and ensure data consistency. In this article, we demonstrate how to query tw
3 min read
Left join using data.table in R
The data. table package in R is one of the best data manipulation tools that enable users to manage big data with so much ease and flexibility. One of its essential operations is the join, particularly the left join. This article will explore how to perform a left join using data.table, its advantag
6 min read
How to Write a SQL Query For a Specific Date Range and Date Time?
Managing date and time in SQL is a critical task for many real-world applications, especially when handling time-sensitive data such as logs, timestamps, and event records. SQL provides various tools and data types to work with date and time effectively. In this guide, we will focus on working with
4 min read