MySQL - Intersect Operator

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);

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')