MySQL Tutorial

This SQL tutorial is structured for beginners to guide them from the foundational concepts to advanced data manipulation and querying techniques in SQL. By the end of this tutorial, you will have developed a robust understanding of SQL, equipping you with the knowledge to efficiently manage and analyze data across various database systems. This guide sets the stage for your growth into a skilled data professional, ready to tackle complex data challenges and contribute to the field of data analysis and database management.

 Intersect Operator
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 
PHPNodeJSJavaPython
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);
Example
Following are the implementations of this operation in various programming languages 
PHPNodeJSJavaPython
$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       
﻿
﻿
﻿
﻿
﻿

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

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

MySQL Basics

MySQL Databases

MySQL Users

MySQL Tables

MySQL Queries

MySQL Views

MySQL Indexes

MySQL Operators and Clauses

MySQL Joins

MySQL Keys

MySQL Triggers

MySQL Data Types

MySQL Regular Expressions

MySQL Fulltext Search

MySQL Functions & Operators

MySQL Misc Concepts

MySQL Useful Resources

MySQL Tutorial

Intersect Operator

MySQL INTERSECT Operator

Syntax

Example

Output

INTERSECT with BETWEEN Operator

Example

Output

INTERSECT with IN Operator

Example

Output

INTERSECT with LIKE Operator

Example

Output

Intersect Operator Using Client Program

Syntax

Example

Output