Prev Next
- The SQL INTERSECT clause is used to combine the two tables together. There will be 2 separate SELECT SQL’s those are combined using INTERSECT clause.
- INTERSECT clause selects the records those are found in both tables (i.e. returned by both SELECT SQLs), eliminate all other records and displays in the output.
- In SQL INTERSECT clause, both SELECT statements should have the same number of columns and in the same order and in similar data type.
SQL Syntax for INTERSECT:
| SQL Syntax for INTERSECT | SELECT column1, column2, etc FROM table1 INTERSECT SELECT column1, column2, etc FROM table2; |
Please consider following 2 tables with few records as given below.
Table1:
Table name (for example): student1
Column names in this table: Student_ID, Student_name, City and Age
Available records: 4 rows
Table2:
Table name (for example): student2
Column names in this table: Student_ID, Department, College and Rank
Available records: 4 rows
Example for how to use SQL INTERSECT:
SQL query:
SELECT Student_ID, City FROM student1
INTERSET
SELECT Student_ID, City FROM student2;
Description:
In above SQL INTERSECT, 2 SELECT SQL’s are executed, and then INTERSECT verifies the result set of both SELECT queries and returns only common records available in both table.
SQL query Output:
- Output of SQL statement – SELECT Student_ID, City FROM student1;
- Output of SQL statement – SELECT Student_ID, City FROM student2;
- Final Output using INTERSECT: (Total records – 3)
