Intersect Operator in MariaDB

MariaDB, a popular open-source relational database management system (RDBMS), offers a plethora of powerful features for data manipulation and querying. Among these features is the Intersect operator, a valuable tool for performing set operations on query results. In this article, We will learn bout the intersect operator in MongoDB along with various examples and so on.

Intersect Operator in MariaDB

The Intersect operator is an operator that is used to retrieve the common records from two or more tables. It works by comparing the result sets of two or more queries and returning only the rows that appear in all result sets. It makes the intersect a useful operator for finding common elements between datasets.

Syntax:

The syntax for using the Intersect operator in MariaDB is straightforward:

SELECT col1, col2, ... FROM table1 INTERSECT SELECT col1, col2, ... FROM table2;

Explanation:

• SELECT column1, column2, … specifies the columns you want to retrieve from the tables.
• FROM table1 specifies the first table or query whose result set you want to compare.
• INTERSECT is the Intersect operator.
• SELECT column1, column2, … specifies the columns you want to retrieve from the second table or query.
• FROM table2 specifies the second table or query.

It’s important to note that the number of columns and their data types in the SELECT queries must match for the Intersect operator to work correctly.

The following picture illustrates the intersect operation:

Intersection

Examples of Intersect Operator in MariaDB

Let’s understand with the use of the Intersect operator with some practical examples. To understand the Intersect Operator in good manner, We need table on which we will perform various operations. Let’s create tables and insert some data into it.

Create table employee_a:

CREATE TABLE employees_a (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
last_name VARCHAR(50),
department VARCHAR(100),
salary DECIMAL(10, 2)
);

Insert data:

INSERT INTO employees_a (employee_id, first_name, last_name, department, salary) VALUES 
(1, 'Minal', 'Pandey', 'Sales',50000.00),
(2, 'Kavya','Sharma','IT',40000.00);

Output:

employee_a Table

Create table employee_b:

CREATE TABLE employees_b (
employee_id INT PRIMARY KEY,
first_name VARCHAR(50),
department VARCHAR(100),
salary DECIMAL(10, 2)
);

Insert data:

INSERT INTO employees_b (employee_id, first_name, department,salary) VALUES 
(1, 'Minal', 'Sales',50000.00),
(2, 'Vardhana', 'Sales',40000.00);

Output:

employee_b Table

Example 1: Return Single Field Using INTERSECT Operator

Suppose we have two tables, employees_a and employees_b, both containing information about customers. We want to find employees who exist in both tables.

Query:

SELECT first_name FROM employees_a
INTERSECT
SELECT first_name FROM employees_b;

Output:

Single Field

Explanation: In the above query, We uses the INTERSECT operator to retrieve the common first_name values from both employees_a and employees_b tables. As we can see in the output it return only the single field first_name Minal which is common in both the tables.

Example 2: Intersecting Multiple Fields

We can also use the Intersect operator to find common records among multiple queries.

Query:

(SELECT employee_id, first_name FROM employees_a WHERE department = 'Sales')
INTERSECT
(SELECT employee_id, first_name FROM employees_b WHERE department = 'Sales')

Output:

Multiple Fields

Explanation:

• The first SELECT statement gets the employee_id and first name from employees_a where department is Sales.
• The second SELECT statement selects the employee_id and first name from employees_b for which department is Sales.
• Then the INTERSECT operator guarantees that only rows with equal employee_id and first_name values from both result sets are returned as a final result.

Example 3: INTERSECT Operator with WHERE Clause

Query:

SELECT employee_id, first_name, salary  
FROM employees_a 
INTERSECT  
SELECT employee_id, first_name, salary  
FROM employees_b  
WHERE salary > 1500;  

Explanation:

• The INTERSECT operator is applied to merge the output of two SELECT statements, showing only those rows that appear in both result sets.
• The first SELECT statement selects the first_name from employees_a.
• The second SELECT statement selects the first_name from employees_b but only includes rows WHERE salary is more than 1500.
• The INTERSECT operator then ensures that only the first_name values which are present in both result sets appear as final results.

Output:

Intersect Using WHERE Clause

Explanation: As we can see below only the Minal is common in both the tables so it will return only that.

Example 4: Intersecting Results of Subqueries

Query:

(SELECT employee_id, first_name, salary
FROM employees_a
WHERE salary > 50000)
INTERSECT
(SELECT employee_id, first_name, salary
FROM employees_b
WHERE salary > 50000);

Output:

Intersecting Results of Subqueries

Explanation:

• We use subqueries to first select employees who have a salary higher than 50000 in both tables.
• The INTERSECT operator finally makes sure that only employees who satisfy the condition in both result sets are returned.
• The query gives the employee_id, first name and salary of employees with a salary above 50000 in both tables.

Conclusion

The Intersect operator in MariaDB provides a powerful means of finding common records between two or more queries. By understanding its syntax and usage, database developers and administrators can efficiently perform set operations. Intersect operator is help when we have large dataset to find out same record present in datasets. We can easily get the desired output.