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 query.

Syntax:

SELECT * FROM table_name WHERE
column_name=( SELECT column_name FROM table_name);

Query written after the WHERE clause is the subquery in above syntax.

Now, for the demonstration follow the below steps:

Step 1: Create a database

we can use the following command to create a database called geeks.

Query:

CREATE DATABASE geeks;

Step 2: Use database

Use the below SQL statement to switch the database context to geeks:

Query:

USE geeks;

Step 3: Table definition

We have two tables named 'demo_table1' and 'demo_table2' in our geek’s database.

Query(demo_table1):

CREATE TABLE demo_table1(
NAME VARCHAR(20),
AGE int,
CITY VARCHAR(10),
INCOME int);

Query(demo_table2):

CREATE TABLE demo_table2(
NAME VARCHAR(20),
AGE int,
INCOME int);

Step 4: Insert data into a table

Query (demo_table1):

INSERT INTO demo_table1 VALUES
('Romy',23,'Delhi',400000),
('Pushkar',23,'Delhi',700000),
('Nikhil',24,'Punjab',350000),
('Rinkle',23,'Punjab',600000),
('Samiksha',23,'Banglore',800000),
('Ashtha',24,'Banglore',300000),
('Satish',30,'Patna',450000),
('Girish',30,'Patna',5500000),
('Ram', 20 , 'Patna',650000),
('Raj', 12, 'Delhi',380000);

Query(demo_table2):

INSERT INTO demo_table2 VALUES
('Fanny',25,600000 ),
('Prem', 30,450000),
('Preeti',21,250000 ),
('Samita',32,440000),
('Ozymandias',34,650000);

Step 5: View the content of the table.

Execute the below query to see the content of the table.

Query:

SELECT * FROM demo_table1;

Output:

Query:

SELECT * FROM demo_table2;

Output:

Step 6: Filter table using another table

For the demonstration, we will filter demo_table1 data whose INCOME is greater than maximum INCOME in semo_table2.

 To get the maximum salary from demo_table2:

Query:

SELECT MAX(INCOME) FROM demo_table2;

Above query used will be used as subquery to filter the demo_table1.

Final Query:

SELECT * FROM demo_table WHERE INCOME > (SELECT MAX(INCOME) FROM demo_table2);

Output:

From image you can see that data from demo_table1 is filtered out having INCOME more than the 650000 (maximum income value in demo_table2 ).