{"id":5256,"date":"2014-05-22T05:51:55","date_gmt":"2014-05-22T12:51:55","guid":{"rendered":"http:\/\/www.mysqltutorial.org\/?page_id=5256"},"modified":"2023-10-07T05:46:14","modified_gmt":"2023-10-07T12:46:14","slug":"mysql-intersect","status":"publish","type":"page","link":"https:\/\/www.mysqltutorial.org\/mysql-basics\/mysql-intersect\/","title":{"rendered":"MySQL INTERSECT"},"content":{"rendered":"\n

Summary<\/strong>: in this tutorial, you will learn about the MySQL INTERSECT<\/code> operator and how to use it to find the common rows of multiple queries.<\/p>\n\n\n\n

Notice that MySQL supported INTERSECT<\/code> operator in MySQL 8.0.31.<\/p>\n\n\n\n

Introduction to the MySQL INTERSECT operator<\/h2>\n\n\n\n

The INTERSECT<\/code> operator is a set operator that returns the common rows of two or more queries.<\/p>\n\n\n\n

Here’s the syntax of the INTERSECT<\/code> operator:<\/p>\n\n\n

query1\nINTERSECT [ALL | DISTINCT]\nquery2;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
The INTERSECT<\/code> operator compares the result sets of two queries and returns the common rows.<\/p>\n\n\n\n
To use the INTERSECT<\/code> operator for the queries, follow these rules:<\/p>\n\n\n\n
    \n
  1. The order and the number of columns in the select list of the queries must be the same.<\/li>\n\n\n\n
  2. The data types of the corresponding columns must be compatible.<\/li>\n<\/ol>\n\n\n\n
    The INTERSECT<\/code> operator uses the DISTINCT<\/code> by default. This means that the DISTINCT<\/code> removes duplicates from either side of the intersection. If you want to retain duplicates, you explicitly specify the ALL<\/code> option.<\/p>\n\n\n\n
    The following diagram illustrates the INTERSECT<\/code> operator.<\/p>\n\n\n
    \n
    \"MySQL<\/figure>\n<\/div>\n\n\n
    In this diagram, the left query produces a result set of (1,2,3) and the right query returns a result set of (2,3,4). The INTERSECT<\/code> operator returns the common rows, which in this case are (2,3).<\/p>\n\n\n\n
    Unlike the UNION<\/a><\/code> operator, which combines the rows of two queries, the INTERSECT<\/code> operator returns the common rows between queries.<\/p>\n\n\n\n
    MySQL INTERSECT operator examples<\/h2>\n\n\n\n
    Let’s take an example of using the INTERSECT<\/code> operator.<\/p>\n\n\n\n
    Setting up sample tables<\/h3>\n\n\n\n
    First, create tables<\/a> t1<\/code> and t2<\/code>, and insert data<\/a> into both tables:<\/p>\n\n\n
    CREATE<\/span> TABLE<\/span> t1 (\n    id<\/span> INT<\/span> PRIMARY KEY<\/span>\n);\n\nCREATE<\/span> TABLE<\/span> t2 LIKE<\/span> t1;\n\nINSERT<\/span> INTO<\/span> t1(id<\/span>) VALUES<\/span>(1<\/span>),(2<\/span>),(3<\/span>);\n\nINSERT<\/span> INTO<\/span> t2(id<\/span>) VALUES<\/span>(2<\/span>),(3<\/span>),(4<\/span>);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Second, query the data from the t1<\/code> table:<\/p>\n\n\n
    SELECT<\/span> id<\/span> FROM<\/span> t1;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    +----+\n| id |\n+----+\n|  1 |\n|  2 |\n|  3 |\n+----+<\/pre>\n\n\n\n
    Third, query the data from the t2<\/code> table:<\/p>\n\n\n
    SELECT<\/span> id<\/span> FROM<\/span> t2;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    +----+\n| id |\n+----+\n|  2 |\n|  3 |\n|  4 |\n+----+<\/pre>\n\n\n\n
    1) MySQL INTERSECT operator example<\/h3>\n\n\n\n
    The following example uses the INTERSECT<\/code> operator to get a result set that contains the rows common to both t1<\/code> and t2<\/code> tables:<\/p>\n\n\n
    SELECT<\/span> id<\/span> FROM<\/span> t1\nINTERSECT<\/span>\nSELECT<\/span> id<\/span> FROM<\/span> t2;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    +----+\n| id |\n+----+\n|  2 |\n|  3 |\n+----+<\/pre>\n\n\n\n
    The query is equivalent to the following query that uses the DISTINCT<\/code> explicitly:<\/p>\n\n\n
    SELECT<\/span> id<\/span> FROM<\/span> t1\nINTERSECT<\/span> DISTINCT<\/span>\nSELECT<\/span> id<\/span> FROM<\/span> t2;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    2) A practical MySQL INTERSECT example<\/h3>\n\n\n\n
    We’ll use the customers<\/code> and employees<\/code> tables from the sample database<\/a> for the demonstration:<\/p>\n\n\n\n
    \"\"<\/figure>\n\n\n\n
    The following query uses the INTERSECT<\/code> operator to find the common first names of customers and employees:<\/p>\n\n\n
    SELECT<\/span> \r\n    firstName\r\nFROM<\/span>\r\n    employees \r\nINTERSECT<\/span>\r\nSELECT<\/span> \r\n    contactFirstname\r\nFROM<\/span>\r\n    customers;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Output:<\/p>\n\n\n
    +-----------+\r\n| firstName |\r\n+-----------+\r\n| Mary      |\r\n| Jeff      |\r\n| William   |\r\n| Leslie    |\r\n| Julie     |\r\n| Steve     |\r\n| Peter     |\r\n| Mart\u00edn    |\r\n+-----------+<\/code><\/span><\/pre>\n\n\n
    3) Using INTERSECT operator with ORDER BY clause<\/h3>\n\n\n\n
    To sort the result set returned by the INTERSECT<\/code> operator, you use the ORDER BY<\/a><\/code> clause. MySQL uses the columns of the first query as the column name of the result set. <\/p>\n\n\n\n
    For example, the following query uses the INTERSECT<\/code> operator to find the common first names of employees and customers and sort them in ascending order:<\/p>\n\n\n
    SELECT<\/span> \r\n    firstName\r\nFROM<\/span>\r\n    employees \r\nINTERSECT<\/span>\r\nSELECT<\/span> \r\n    contactFirstname\r\nFROM<\/span>\r\n    customers\r\nORDER<\/span> BY<\/span> firstName;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Output:<\/p>\n\n\n
    +-----------+\r\n| firstName |\r\n+-----------+\r\n| Jeff      |\r\n| Julie     |\r\n| Leslie    |\r\n| Mart\u00edn    |\r\n| Mary      |\r\n| Peter     |\r\n| Steve     |\r\n| William   |\r\n+-----------+<\/code><\/span><\/pre>\n\n\n
    4) Using INTERSECT operator with ALL option example<\/h3>\n\n\n\n
    The following example uses the INTERSECT<\/code> operator with the ALL<\/code> option that finds the common first names of employees and customers and retains the duplicates:<\/p>\n\n\n
    SELECT<\/span> \r\n    firstName\r\nFROM<\/span>\r\n    employees \r\nINTERSECT<\/span> ALL<\/span>\r\nSELECT<\/span> \r\n    contactFirstname\r\nFROM<\/span>\r\n    customers\r\nORDER<\/span> BY<\/span> firstName;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\n\n\n
    Output:<\/p>\n\n\n
    +-----------+\r\n| firstName |\r\n+-----------+\r\n| Jeff      |\r\n| Julie     |\r\n| Leslie    |\r\n| Leslie    |\r\n| Mart\u00edn    |\r\n| Mary      |\r\n| Peter     |\r\n| Steve     |\r\n| William   |\r\n+-----------+<\/code><\/span><\/pre>\n\n\n
    Summary<\/h2>\n\n\n\n
      \n
    • Use the MySQL INTERSECT<\/code> operator to find the rows that are common to multiple query results.<\/li>\n\n\n\n
    • Use INTERSECT DISTINCT<\/code> to remove the duplicates from the result sets and INTERSECT ALL<\/code> to retain the duplicates.<\/li>\n\n\n\n
    • The INTERSECT<\/code> operator uses DISTINCT<\/code> by default.<\/li>\n<\/ul>\n
      \n\t
      \n\t\t
      Was this tutorial helpful? <\/div>\n\t\t
      \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
      \n\t\t
      \n\t\t\t
      <\/div>\n\t\t\t\n\t\t\t