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.
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.
CREATE DATABASE geeks;
Step 2: Use database
Use the below SQL statement to switch the database context to geeks:
Step 3: Table definition
We have two tables named ‘demo_table1’ and ‘demo_table2’ in our geek’s database.
CREATE TABLE demo_table1( NAME VARCHAR(20), AGE int, CITY VARCHAR(10), INCOME int);
CREATE TABLE demo_table2( NAME VARCHAR(20), AGE int, INCOME int);
Step 4: Insert data into a table
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);
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.
SELECT * FROM demo_table1;
SELECT * FROM demo_table2;
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:
SELECT MAX(INCOME) FROM demo_table2;
Above query used will be used as subquery to filter the demo_table1.
SELECT * FROM demo_table WHERE INCOME > (SELECT MAX(INCOME) FROM demo_table2);
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 ).