Hello. In this tutorial, we will learn the INTERSECT operator in SQL and will use the PostgreSQL database running on Docker.
1. Introduction
SQL stands for Structured Query Language and is used to extract and organize data stored in relational databases like MySQL, PostgreSQL, Oracle, etc. A relational database consists of rows and columns that allow fetching specific information from databases that can be used later for analysis. In real-time SQL manages a large amount of data that is written and read simultaneously and any query that reaches the SQL server is processed into three parts –
- Parsing – Process to check the SQL query syntax
- Binding – Process to check the SQL query semantics
- Optimization – Process to generate the SQL query execution plan
1.1 Usage of SQL
Structured Query Language (popularly known as SQL) is commonly used by data analysts and data science professionals and is helpful too –
- Execute queries against the database
- Retrieve data from the database
- Insert new records into the database
- Update existing records in the database
- Created stored procedures, functions, and materialized views in the database
- Create users and grant permissions
- Set permissions on tables, stored procedures, functions, and materialized views
1.2 INTERSECT operator in SQL
- The INTERSECT operator in SQL combines two
SELECTstatements and returns only the dataset common in both statements - Follow the two rules –
- The number and order of columns in both queries have to be the same
- Data types of corresponding columns from both the
SELECTstatements must be compatible with each other
The INTERSECT operator is represented by the below syntax.
Query Syntax
select column1 [, column2 ] from table1 [, table2 ] [where condition] intersect select column1 [, column2 ] from table1 [, table2 ] [where condition];
2. SQL INTERSECT operator
Let us dive into some practice implementation on the PostgreSQL database.
2.1 Pre-requirement – Postgres Setup
Usually, setting up the database is a tedious step but with Docker, it is a simple process. You can watch the video available at this link to understand the Docker installation on Windows OS. Once done open the terminal and trigger the below command to set and run PostgreSQL.
Docker commands
-- command to run postgres on docker – -- remember to change the password -- docker run -d -p 5433:5432 -e POSTGRES_PASSWORD= --name postgres postgres -- command to stop the Postgres docker container -- docker stop postgres -- command to remove the Postgres docker container -- docker rm postgres
Remember to enter the password of your choice. If everything goes well the PostgreSQL database server would be up and running on a port number – 5433 and you can connect with the Dbeaver GUI tool for connecting to the server.
2.2 Setting up pre-requisite data
Create the database named learning and add the following table and data to the tables with the help of the below SQL script.
Pre-requisite SQL script
-- customers
create table customers(id serial primary key, name varchar, country varchar, city varchar);
insert into customers (name, country, city)
values
('raltofts0', 'Thailand', 'Nang Rong'),('fgariff1', 'China', 'Yuefeng'),('csilcock2', 'France', 'Lacroix-Saint-Ouen'),('mhasluck3', 'Indonesia', 'Cibaregbeg Dua'),('bmerrall4', 'Russia', 'Ust’-Uda'),('scuddon5', 'Greece', 'Nestório'),('amichelet6', 'Vietnam', 'Quán Hành'),('hpoon7', 'Colombia', 'Sevilla'),('rchugg8', 'Madagascar', 'Sakaraha'),('pleyland9', 'Seychelles', 'Anse Boileau');
select id, name, country, city from customers;
-- branches
create table branches(id serial primary key, country varchar, city varchar);
insert into branches (country, city)
values
('Russia', 'Neftegorsk'),('Indonesia', 'Krajan Dua Dawuan Wetan'),('Russia', 'Perm'),('China', 'Daxing'),('China', 'Bianzhuang'),('Argentina', 'Apóstoles'),('China', 'Jianrao'),('Yemen', 'Al Jabīn'),('Finland', 'Muhos'),('Philippines', 'Kaytitinga'),('China', 'Tekes'),('Indonesia', 'Nanger'),('Russia', 'Podol’sk'),('Russia', 'Lensk'),('Finland', 'Orimattila'),('Vietnam', 'Phước An'),('China', 'Dadamtu'),('Poland', 'Potęgowo'),('Thailand', 'Fao Rai'),('Pakistan', 'Jhumra');
select id, country, city from branches;
Once the SQL script is executed the mock data will be successfully added to the customers and branches tables respectively.
2.3 Playing with the INTERSECT operator
Use the below SELECT keyword to understand the INTERSECT SQLoperator.
UNION operator
-- sql intersect -- select query will display the common country records from both tables select country from customers intersect select country from branches order by country; -- select query will display the common country and city records from both tables select country, city from customers intersect select country, city from branches order by city;
If everything goes well the result of the SQL query will be shown on the console and the usage of the INTERSECT operator can be practically understood.
3. Summary
In this tutorial, we learned the brief of SQL and SQL INTERSECT operator via different examples. You can download the SQL scripts used in this tutorial from the Downloads section.
4. Download the SQL INTERSECT scripts
This was a tutorial to understand the INTERSECT operator in SQL.
You can download the full source code of this example here: SQL INTERSECT operator
