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.
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.