ORACLE
ORACLE JOINS
INNER JOIN
OUTER JOIN
EQUI JOIN
SELF JOIN
CROSS JOIN
ANTI JOIN
SEMI JOIN
ORACLE TUTORIAL
Oracle Introduction
What Is Oracle
ORACLE TABLES
Create Tables
Create Table As
Alter Table
Drop Table
Global Temorary Tables
Local Temorary Tables
ORACLE VIEWS
Create View
ORACLE QUERIES
SELECT
INSERT
INSERT ALL
UPDATE
DELETE
TRUNCATE TABLE
ORACLE CLAUSES
DISTINCT
FROM
ORDER BY
GROUP BY
HAVING
ORACLE OPERATORS
UNION
UNION ALL
INTERSECT
MINUS
ORACLE ADVANCE
PROCEDURES
FUNCTION
CURSOR
TRIGGER

SEMI JOIN



Oracle - Semi Join


Semi-join is introduced in Oracle 8.0. It provides an efficient method of performing a WHERE EXISTS sub-query.
A semi-join returns one copy of each row in first table for which at least one match is found.
Semi-joins are written using the EXISTS construct.


Oracle Semi Join Example


Let's take two tables "departments" and "customer"


Departments table

DEPARTMENT_IDDEPARTMENT_NAME
1A
2B
3C
4D

Customer table

CUSTOMER_IDFIRST_NAMELAST_NAMEDEPARTMENT_ID
1Nirakshsarkar-
2NiroopParitala-
3SweejyaMehtha-
4DeepuNehra1

Execute this query

SELECT   departments.department_id, departments.department_name  
        FROM     departments  
        WHERE    EXISTS  
                 (  
                 SELECT 1  
                 FROM   customer  
                 WHERE customer.department_id = departments.department_id  
                 )  
        ORDER BY departments.department_id;  

Output :

DEPARTMENT_IDDEPARTMENT_NAME
1A

Difference between anti-join and semi-join

While a semi-join returns one copy of each row in the first table for which at least one match is found, an anti-join returns one copy of each row in the first table for which no match is found.



What Is Oracle

What Is Oracle

posted on 2019-11-29 01:11:21 - ORACLE Tutorials


TRIGGER

ORACLE - Trigger

posted on 2019-11-28 22:14:22 - ORACLE Tutorials


CURSOR

ORACLE - Cursor

posted on 2019-11-28 22:13:54 - ORACLE Tutorials


MySQL TRIGGERS

MySQL - Triggers

posted on 2019-11-29 21:44:07 - mysql Tutorials


Grant_ Revoke Privilege

MySQL - Grant_ Revoke Privilege

posted on 2019-11-26 23:15:04 - mysql Tutorials


MySQL Vs SQL

MySQL Vs SQL

posted on 2019-11-25 05:02:26 - mysql Tutorials