Home >> Oracle >> Semi Join in Oracle

Semi Join in Oracle

Semi-join between two tables returns rows from the first table where matches at least one found in value with another table.

Semi-join using the EXISTS constructs.

Syntax :

SELECT column_name(s) FROM table_1 WHERE EXISTS (SELECT values FROM table_2 WHERE table_2.column = table_1.column);

Example

employee table
id name salary city
1 Ram 200000 Delhi
2 Shyam 10000 Ghaziabad
3 Ghanshyam 30000 Allahabad
4 Ramesh 200000 Noida

employee_details table
id address city
10 New Delhi-110096 Delhi
11 Ghaziabad-23355 Ghaziabad
12 Allahabad 230001 Allahabad
13 Lucknow-234000 Lucknow

Query

SELECT `employee`.`id`,`employee`.`name` FROM `employee` WHERE
EXISTS (SELECT 'Lucknow' FROM `employee_details` WHERE `employee_details`.`city`=`employee`.`city`)

Output
id name
1 Ram
2 Shyam
3 Ghanshyam