x

SQL – INTERSECT

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

sql-table1

Table2:

Table name (for example): student2
Column names in this table: Student_ID, Department, College and Rank
Available records: 4 rows


sql-table53

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;

sql-table49

  • Output of SQL statement – SELECT Student_ID, City FROM student2;

sql-table54

  • Final Output using INTERSECT: (Total records – 3)

sql-table55

Prev     Next



Like it? Please Spread the word!