mysql

Khóa học miễn phí MySQL – INTERSECT Operator nhận dự án làm có lương

Posted on by user

MySQL – Intersect Operator

Table of content

In mathematical set theory, the intersection of two sets is a set that contains only those elements that are common to both sets. In other words, the intersection of two sets is a set of elements that exist in both sets.

If we perform the intersection operation on both sets using the INTERSECT operator, it displays the common rows from both tables. This operator removes the duplicate rows from the final result set.

MySQL INTERSECT Operator

In MySQL, the INTERSECT operator is used to return the records that are identical/common between the result sets of two SELECT (tables) statements.

However, the INTERSECT operator works only if both the SELECT statements have an equal number of columns with same data types and names.

Syntax

Following is the syntax of INTERSECT operator in MySQL −

SELECT column1, column2,..., columnN
FROM table1, table2,..., tableN
INTERSECT
SELECT column1, column2,..., columnN
FROM table1, table2,..., tableN

Example

First of all, let us create a table named STUDENTS using the following query −

CREATE TABLE STUDENTS(
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   HOBBY VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   PRIMARY KEY(ID)
);

Here, we are inserting some values into the table using the INSERT statement.

INSERT INTO STUDENTS VALUES
(1, ''Vijay'', ''Cricket'', 18),
(2, ''Varun'', ''Football'', 26),
(3, ''Surya'', ''Cricket'', 19),
(4, ''Karthik'', ''Cricket'', 25),
(5, ''Sunny'', ''Football'', 26),
(6, ''Dev'', ''Cricket'', 23);

The table is created as follows −

ID NAME HOBBY AGE
1 Vijay Cricket 18
2 Varun Football 26
3 Surya Cricket 19
4 Karthik Cricket 25
5 Sunny Football 26
6 Dev Cricket 23

Now, let us create another table with name ASSOCIATES using the following query −

CREATE TABLE ASSOCIATES(
   ID INT NOT NULL,
   NAME VARCHAR(20) NOT NULL,
   SUBJECT VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   HOBBY VARCHAR(20) NOT NULL,
   PRIMARY KEY(ID)
);

Here, we are inserting some values into the table using the INSERT statement −

INSERT INTO ASSOCIATES VALUES
(1, ''Naina'', ''Maths'', 24, ''Cricket''),
(2, ''Varun'', ''Physics'', 26, ''Football''),
(3, ''Dev'', ''Maths'', 23, ''Cricket''),
(4, ''Priya'', ''Physics'', 25, ''Cricket''),
(5, ''Aditya'', ''Chemistry'', 21, ''Cricket''),
(6, ''Kalyan'', ''Maths'', 30, ''Football'');

The table is created as follows −

ID NAME SUBJECT AGE HOBBY
1 Naina Maths 24 Cricket
2 Varun Physics 26 Football
3 Dev Maths 23 Cricket
4 Priya Physics 25 Cricket
5 Aditya Chemistry 21 Cricket
6 Kalyan Maths 30 Football

Now, we return the common records from both the tables using the following query −

SELECT NAME, AGE, HOBBY FROM STUDENTS
INTERSECT
SELECT NAME, AGE, HOBBY FROM ASSOCIATES;

Output

The output is obtained as follows −

NAME AGE HOBBY
Varun 26 Football
Dev 23 Cricket

INTERSECT with BETWEEN Operator

The MySQL INTERSECT operator can be used with the BETWEEN operator to find the rows that exist within the specified range.

Example

In the following query, we are retrieving the records that are common in both tables. In addition; we are retrieving the records who are aged between 25 and 30 −

SELECT NAME, AGE, HOBBY FROM STUDENTS
WHERE AGE BETWEEN 25 AND 30
INTERSECT
SELECT NAME, AGE, HOBBY FROM ASSOCIATES
WHERE AGE BETWEEN 20 AND 30;

Output

On executing the given program, the output is displayed as follows −

NAME AGE HOBBY
Varun 26 Football

INTERSECT with IN Operator

In MySQL, we can use the INTERSECT operator with IN operator to find the common rows that have the specified values. The IN operator is used to filter a result set based on a list of specified values.

Example

In the following query, we are trying to return the common records from both tables. In addition; we are using th IN operator to retrieve the records whose hobby is “Cricket”.

SELECT NAME, AGE, HOBBY FROM STUDENTS
WHERE HOBBY IN(''Cricket'')
INTERSECT
SELECT NAME, AGE, HOBBY FROM ASSOCIATES
WHERE HOBBY IN(''Cricket'');

Output

The output for the program above is produced as given below −

NAME AGE HOBBY
Dev 23 Cricket

INTERSECT with LIKE Operator

The LIKE operator is used to perform pattern matching on a string value.

We can use the LIKE operator with the INTERSECT operator in MySQL to find the common rows that match the specified pattern.

Example

In the following query, we are using the wildcard ”%” with the LIKE operator to fetch the names with ”v” from the common names of both tables.

SELECT NAME, AGE, HOBBY FROM STUDENTS
WHERE NAME LIKE ''v%''
INTERSECT
SELECT NAME, AGE, HOBBY FROM ASSOCIATES
WHERE NAME LIKE ''v%

Output

Let us compile and run the program, to produce the following result −

NAME AGE HOBBY
Varun 26 Football

Intersect Operator Using Client Program

In addition to executing the Intersect Operator in MySQL server, we can also execute the INTERSECT operator on a table using a client program.

Syntax

Following are the syntaxes of the Intersect Operator in MySQL table in various programming languages −

To execute the Intersect operator in MySQL table through a PHP program, we need to execute INTERSECT statement using the query() function of mysqli connector.

$sql = "SELECT column1, column2,..., columnN FROM table1, table2,..., 
tableN INTERSECT SELECT column1, column2,..., columnN 
FROM table1, table2,..., tableN";  
$mysqli->query($sql);

To execute the Intersect operator in MySQL table through a JavaScript program, we need to execute INTERSECT statement using the query() function of mysql2 connector.

sql = "SELECT column1, column2,..., columnN FROM table1, table2,..., tableN
INTERSECT SELECT column1, column2,..., columnN 
FROM table1, table2,..., tableN";
con.query(sql);

To execute the Intersect operator in MySQL table through a Java program, we need to execute INTERSECT statement using the executeQuery() function of JDBC type 4 driver.

String sql = "SELECT column1, column2,..., columnN FROM table1, table2,..., tableN 
INTERSECT SELECT column1, column2,..., columnN 
FROM table1, table2,..., tableN";
statement.executeQuery(sql);

To execute the Intersect operator in MySQL table through a Python program, we need to execute INTERSECT statement using the execute() function provided by MySQL Connector/Python.

intersect_query = SELECT column1, column2,..., column
FROM table1, table2,..., tableN
INTERSECT SELECT column1, column2,..., column
FROM table1, table2,..., tableN
cursorObj.execute(intersect_query);

Example

Following are the implementations of this operation in various programming languages −

$dbhost = ''localhost
$dbuser = ''root
$dbpass = ''password
$dbname = ''TUTORIALS
$mysqli = new mysqli($dbhost, $dbuser, $dbpass, $dbname);
if($mysqli->connect_errno ) {
   printf("Connect failed: %s
", $mysqli->connect_error);
   exit();
}
//printf(''Connected successfully.
'');
$sql = "SELECT NAME, AGE, HOBBY FROM STUDENTS INTERSECT SELECT NAME, AGE, HOBBY FROM ASSOCIATES;";
$result = $mysqli->query($sql);
if ($result->num_rows > 0) {
   printf("Table records: n");
   while($row = $result->fetch_assoc()) {
      printf("NAME %s, AGE %d, HOBBY %s",
      $row["NAME"],
      $row["AGE"],
      $row["HOBBY"],);
      printf("n");         
   }
} else {
   printf(''No record found.
'');
}
mysqli_free_result($result);
$mysqli->close();

Output

The output obtained is as follows −

Table records:
NAME Varun, AGE 26, HOBBY Football
NAME Dev, AGE 23, HOBBY Cricket       

var mysql = require(''mysql2'');
var con = mysql.createConnection({
    host: "localhost",
    user: "root",
    password: "Nr5a0204@123"
});

  //Connecting to MySQL
  con.connect(function (err) {
  if (err) throw err;
  console.log("Connected!");
  console.log("--------------------------");

  //Creating a Database
  sql = "create database TUTORIALS"
  con.query(sql);

  //Select database
  sql = "USE TUTORIALS"
  con.query(sql);

  //Creating STUDENTS table
  sql = "CREATE TABLE STUDENTS(ID INT NOT NULL,NAME VARCHAR(20) NOT NULL,HOBBY VARCHAR(20) NOT NULL,AGE INT NOT NULL,PRIMARY KEY(ID));"
  con.query(sql);

  //Inserting Records
  sql = "INSERT INTO STUDENTS(ID, NAME, HOBBY, AGE) VALUES(1, ''Vijay'', ''Cricket'',18),(2, ''Varun'',''Football'', 26),(3, ''Surya'', ''Cricket'',19),(4, ''Karthik'',''Cricket'', 25),(5, ''Sunny'',''Football'', 26),(6, ''Dev'', ''Cricket'',23);"
  con.query(sql);

  //Creating ASSOCIATES table
  sql = "CREATE TABLE ASSOCIATES(ID INT NOT NULL,NAME VARCHAR(20) NOT NULL,SUBJECT VARCHAR(20) NOT NULL,AGE INT NOT NULL,HOBBY VARCHAR(20) NOT NULL,PRIMARY KEY(ID));"
  con.query(sql);
  
  //Inserting Records
  sql = "INSERT INTO ASSOCIATES(ID, NAME, SUBJECT, AGE, HOBBY) VALUES(1, ''Naina'',''Maths'', 24, ''Cricket''),(2, ''Varun'',''Physics'', 26, ''Football''),(3, ''Dev'',''Maths'', 23, ''Cricket''),(4, ''Priya'',''Physics'', 25, ''Cricket''),(5,''Aditya'', ''Chemistry'', 21, ''Cricket''),(6,''Kalyan'', ''Maths'', 30, ''Football'');"
  con.query(sql);

  //Using INTERSECT Operator
  sql = "SELECT NAME, AGE, HOBBY FROM STUDENTS INTERSECT SELECT NAME, AGE, HOBBY FROM ASSOCIATES;"
  con.query(sql, function(err, result){
    if (err) throw err
    console.log(result)
  });
});

Output

The output produced is as follows −

Connected!
--------------------------
[
  { NAME: ''Varun'', AGE: 26, HOBBY: ''Football'' },
  { NAME: ''Dev'', AGE: 23, HOBBY: ''Cricket'' }
]  

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class IntersectOperator {
  public static void main(String[] args) {
    String url = "jdbc:mysql://localhost:3306/TUTORIALS";
    String user = "root";
    String password = "password";
    ResultSet rs;
    try {
      Class.forName("com.mysql.cj.jdbc.Driver");
            Connection con = DriverManager.getConnection(url, user, password);
            Statement st = con.createStatement();
            //System.out.println("Database connected successfully...!");
            String sql = "SELECT NAME, AGE, HOBBY FROM STUDENTS INTERSECT SELECT NAME, AGE, HOBBY FROM ASSOCIATES";
            rs = st.executeQuery(sql);
            System.out.println("Table records: ");
            while(rs.next()) {
              String name = rs.getString("NAME");
              String age = rs.getString("AGE");
              String hobby = rs.getString("HOBBY");
              System.out.println("Name: " + name + ", Age: " + age + ", Hobby: " + hobby);
            }
    }catch(Exception e) {
      e.printStackTrace();
    }
  }
}

Output

The output obtained is as shown below −

Table records: 
Name: Varun, Age: 26, Hobby: Football
Name: Dev, Age: 23, Hobby: Cricket  

import mysql.connector
#establishing the connection
connection = mysql.connector.connect(
    host=''localhost'',
    user=''root'',
    password=''password'',
    database=''tut''
)
cursorObj = connection.cursor()
intersect_query = f"""
SELECT NAME, AGE, HOBBY FROM STUDENTS
INTERSECT
SELECT NAME, AGE, HOBBY FROM ASSOCIATES;
"""
cursorObj.execute(intersect_query)
# Fetching all the rows that meet the criteria
filtered_rows = cursorObj.fetchall()
for row in filtered_rows:
    print(row)
cursorObj.close()
connection.close()

Output

Following is the output of the above code −

(''Varun'', 26, ''Football'')
(''Dev'', 23, ''Cricket'')

Khóa học lập trình tại Toidayhoc vừa học vừa làm dự án vừa nhận lương: Khóa học lập trình nhận lương tại trung tâm Toidayhoc

user