**ORACLE INTERSECT **

To compare the rows of two or more Oracle SELECT statements, the Oracle INTERSECT operator is used. After the comparing process, the INTERSECT operator returns the common or intersecting records from the corresponding columns of the selected expressions.

There are however two mandatory conditions for using the INTERSECT operator in Oracle.

- Each SELECT statement must have the same number of expressions.
- Each corresponding expression in the different SELECT statement should be of the same data type.

**Syntax: **

SELECT expr_1, expr_2, ... expr_n FROM table1 WHERE conditions INTERSECT SELECT expr_1, expr_2, ... expr_n FROM table2 WHERE conditions;

** Parameters: **

**expr_1, expr_2, … expr_n: ** It is used to specify the columns of the table which needs to be retrieved.

**table1, table2: ** It is used to specify the name of the tables from which the records need to be retrieved.

**conditions: ** It is used to specify the conditions to be strictly followed for selection.

**Example 1: Fetching a single field from two tables. **

** Students Table:**

STUDENT_ID | NAME | AGE |

1 |
Joy | 20 |

2 |
Smiley | 19 |

3 |
Happy | 21 |

4 |
James | 22 |

5 |
Bond | 25 |

**Teachers Table:**

TEACHER_ID | NAME | AGE |

101 |
James | 30 |

102 |
Bond | 25 |

103 |
Smith | 40 |

** Query: **

SELECT name FROM students INTERSECT SELECT name FROM teachers;

**Output: **

NAME James Bond

**Explanation: **

The ‘students’ and the ‘teachers’ are the already existing tables. After the intersection, the common rows for the ‘name’ field from the ‘students’ table and the ‘teachers’ table would appear.

**Example 2: Fetching multiple fields from two tables. **

** Students Table:**

STUDENT_ID | NAME | AGE |

1 |
Joy | 20 |

2 |
Smiley | 19 |

3 |
Happy | 21 |

4 |
James | 22 |

5 |
Bond | 25 |

**Teachers Table:**

TEACHER_ID | NAME | AGE |

101 |
James | 30 |

102 |
Bond | 25 |

103 |
Smith | 40 |

** Query: **

SELECT name, age FROM students INTERSECT SELECT name, age FROM teachers;

**Output: **

NAME AGE Bond 25

**Explanation: **

The ‘students’ and the ‘teachers’ are the already existing tables. After the intersection, the common rows for the ‘name’ and the ‘age’ fields from the ‘students’ table and the ‘teachers’ table would appear.