Example HTML page

MySQL Intersect operator

In this post we are going to discuss Intersect operator. As discussed in the earlier post INTERSECT operator doesn’t support in MySQL. But we can try it in another way by including some clauses like IN clause or EXISTS clause depends on query.

First let us know what an intersect operator is??

It returns the records which are common in both the tables.

Rules to apply intersect operator:

  • The order and the number of columns must be the same.
  • The data types of the corresponding columns must be same.

Ex: A={10,20,30},B={20,30,40,50}

Intersection of A and B is {20,30}.

To demonstrate this, let’s us go with the following example.

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| captcha            |
| mysql              |
| performance_schema |
| sampledb           |
| sports             |
| test               |
+--------------------+
7 rows in set (0.00 sec)

mysql> use sampledb;
Database changed
mysql> show tables;
+--------------------+
| Tables_in_sampledb |
+--------------------+
| r1                 |
| r2                 |
| table1             |
| table2             |
+--------------------+
4 rows in set (0.00 sec)

mysql> select * from table1;
+--------+----------+
| ref_no | faculty  |
+--------+----------+
|    123 | Sudhakar |
|    234 | Rathnam  |
|    345 | Ramana   |
+--------+----------+
3 rows in set (0.00 sec)

mysql> select * from table2;
+--------+----------+
| ref_no | faculty  |
+--------+----------+
|    567 | Binish   |
|    678 | Rajesh   |
|    789 | Suresh   |
|    123 | Sudhakar |
|    234 | Rathnam  |
+--------+----------+
5 rows in set (0.00 sec)

If you perform intersection for the above two tables you will definitely get an error as follows:

mysql> select * from table1 intersect select * from table2;
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that
corresponds to your MySQL server version for the right syntax to use near 'selec
t * from table2' at line 1

The following are the ways to apply the intersect operator without any error.

Method 1: Using DISTINCT operator and INNER JOIN clause.

The DISTINCT operator removes the duplicate rows. The INNER JOIN clause returns rows from both left and right tables.

Syntax:

select DISTINCT column_name from table_name1 INNER JOIN table_name2 USING(column_name);

Ex:

mysql> select distinct ref_no from table1 inner join table2 using(ref_no);
+--------+
| ref_no |
+--------+
|    123 |
|    234 |
+--------+
2 rows in set (0.06 sec)
mysql> select distinct faculty from table1 inner join table2 using(faculty);
+----------+
| faculty  |
+----------+
| Sudhakar |
| Rathnam  |
+----------+
2 rows in set (0.00 sec)

When you specify the column in first select statement without any column name in extended sub query you will get an error as follows because the rule of intersect violates here.

mysql> select distinct faculty from table1 inner join table2 faculty;
ERROR 1052 (23000): Column 'faculty' in field list is ambiguous
mysql> select distinct faculty from table1 inner join table2;
ERROR 1052 (23000): Column 'faculty' in field list is ambiguous

Method 2: Using IN operator and sub query:

The outer query uses the IN operator (including where clause) to select only values that are in the first result set. The DISTINCT operator ensures that only distinct values are selected. The sub query returns the first result set.

Syntax:

select DISTINCT column_name from table_name1 WHERE column_name IN(select id from table_name2);

Ex:

mysql> select distinct ref_no from table1 where ref_no in(select ref_no from tab
le2);
+--------+
| ref_no |
+--------+
|    123 |
|    234 |
+--------+
2 rows in set (0.06 sec)
mysql> select distinct faculty from table1 where ref_no in(select ref_no from ta
ble2);
+----------+
| faculty  |
+----------+
| Sudhakar |
| Rathnam  |
+----------+
2 rows in set (0.00 sec)

Click here

To watch this tutorial on YouTube channel.

 

Example HTML page

Leave a Reply

Your email address will not be published. Required fields are marked *

Pin It on Pinterest