SQL Query to select Data from Tables Using Join and Where
The aim of this article is to make a simple program to Join two tables using Join and Where clause using MySQL. Below is the method to do the same using MySQL. The prerequisites of this article are MySQL and Apache Server on your computer are installed.
What is a Query in SQL?
A SQL query is a request passed for data/information from a table in a database. This data can be used for various purposes like Training a model, finding the patterns in the data, etc.
What is Join in SQL?
A JOIN query is used to combine rows from two or more tables, based on a single column which can be used to store the same data from both tables. So we join over that point and join rows.
What is the Where Clause in SQL?
WHERE keyword in SQL is used for retrieving data in a result under a certain query. It can also be used to retrieve data by matching patterns like Select all the students whose marks are greater than 90 or select all the data from tables where employees salary is greater than 6 lakhs and less than 12 lakhs.
So we will start by creating a database –
Step 1: Create a Database
CREATE DATABASE w3wiki;
Step 2: Enter this database to use it –
USE w3wiki;
Step 3: 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 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 the schema of the table to ensure the table is correct –
> DESC employee; > DESC dept;
Step 6: Insert the data into the employee table –
INSERT INTO employee VALUES(1, "Devesh", "Beginner@Beginner.com", 1); INSERT INTO employee VALUES(2, "Mayank", "for@Beginner.com", 1); INSERT INTO employee VALUES(3, "Aditya", "Beginner@Beginner.com", 2); INSERT INTO employee VALUES(4, "Divyanshi", "portal@Beginner.com", 2); INSERT INTO employee VALUES(5, "Megha", "is@Beginner.com", 3); INSERT INTO employee VALUES(6, "Himanshi", "a@Beginner.com", 3); INSERT INTO employee VALUES(7, "Tanishka", "computer@Beginner.com", 4); INSERT INTO employee VALUES(8, "Jatin", "science@Beginner.com", 4);
Step 7: Insert data into 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 where and Join –
Example 1: Select all the data of employees who are the HODs of the departments –
SELECT employee.ID, employee.Name, employee.Email FROM employee JOIN dept WHERE employee.ID = dept.hodId;
Output:
Example 2: Select all the data where the department’s profit is greater than 45000 –
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: Select all the data from both the tables using JOIN (cross join) –
SELECT * FROM employee FULL JOIN dept WHERE dept.id > 0;
Example 4: Select all the employees from a department whose sum profit is greater than 5000
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:
Contact Us