How to use IN BETWEEN and UNION operators in SQL

In this tutorial, we will learn how to use IN, BETWEEN and UNION operators in SQL in a simple manner with examples.

IN, BETWEEN and UNION operators in SQL

In this tutorial, we will learn how to make use of them in, between, and union operators in SQL.

How to use the “IN” operator in SQL?

In SQL, the IN is a logical operator. If a value is in a set of values, the IN operator returns true; otherwise, it returns false. In a WHERE clause, the IN operator allows you to define several values. Multiple OR conditions can be simply represented by the IN operator.


Sentiment Analysis Project on eCommerce Product Reviews with Source Code 


Syntax:
SELECT column_name1, column_name2, ... FROM table_name
WHERE column_name IN (value1, value2, ...);

Or you can also use

SELECT column_name1, column_name2, ... FROM table_name
WHERE column_name IN (SELECT statement);

Let us see a few examples:

Code:

SELECT customer_id FROM customers WHERE city IN ("London","Birmingham");

Output:
+-------------+
| customer_id |
+-------------+
|         101 |
|         103 |
+-------------+
2 rows in set (0.00 sec)

How to use the “Between” operator in SQL?

One of SQL's logical operators is the BETWEEN operator. The BETWEEN operator determines if a value falls inside a specified range of values.

Syntax:
SELECT column_name1, column_name2, ... FROM table_name
WHERE column_name BETWEEN value1 AND value2;

Let us retrieve the customer_id and customer_name of the customers whose income lies between (50000000 – 70000000)

Code:

SELECT customer_id, customer_name FROM customers WHERE income BETWEEN 50000000 and 70000000;

Output:
+-------------+---------------+
| customer_id | customer_name |
+-------------+---------------+
|         104 | Michael Gray  |
+-------------+---------------+
1 row in set (0.00 sec)

How to use the Union operator in SQL?

UNION operator merges the results of two or more SELECT queries into a single output set. Within UNION, every SELECT statement must have the same amount of columns. The data types in the columns must also be similar. Every SELECT statement's columns must be in the same order.

Syntax:
SELECT column_name1, column-name2, ... FROM table_name1
UNION ALL
SELECT column_name1, column_name2,.. FROM table_name2;

Or

SELECT column_name1, column-name2, ... FROM table_name1
UNION
SELECT column_name1, column_name2,.. FROM table_name2;

Code:

SELECT city FROM customers
UNION ALL
SELECT city FROM orders
ORDER BY city;


SELECT city FROM customers
UNION
SELECT city FROM orders
ORDER BY city;

What Users are saying..

profile image

Ray han

Tech Leader | Stanford / Yale University
linkedin profile url

I think that they are fantastic. I attended Yale and Stanford and have worked at Honeywell,Oracle, and Arthur Andersen(Accenture) in the US. I have taken Big Data and Hadoop,NoSQL, Spark, Hadoop... Read More

Relevant Projects

Multilabel Classification Project for Predicting Shipment Modes
Multilabel Classification Project to build a machine learning model that predicts the appropriate mode of transport for each shipment, using a transport dataset with 2000 unique products. The project explores and compares four different approaches to multilabel classification, including naive independent models, classifier chains, natively multilabel models, and multilabel to multiclass approaches.

Build an Image Classifier for Plant Species Identification
In this machine learning project, we will use binary leaf images and extracted features, including shape, margin, and texture to accurately identify plant species using different benchmark classification techniques.

Llama2 Project for MetaData Generation using FAISS and RAGs
In this LLM Llama2 Project, you will automate metadata generation using Llama2, RAGs, and AWS to reduce manual efforts.

Census Income Data Set Project-Predict Adult Census Income
Use the Adult Income dataset to predict whether income exceeds 50K yr based oncensus data.

Loan Eligibility Prediction in Python using H2O.ai
In this loan prediction project you will build predictive models in Python using H2O.ai to predict if an applicant is able to repay the loan or not.

NLP Project for Beginners on Text Processing and Classification
This Project Explains the Basic Text Preprocessing and How to Build a Classification Model in Python

Learn How to Build a Logistic Regression Model in PyTorch
In this Machine Learning Project, you will learn how to build a simple logistic regression model in PyTorch for customer churn prediction.

Machine Learning Project to Forecast Rossmann Store Sales
In this machine learning project you will work on creating a robust prediction model of Rossmann's daily sales using store, promotion, and competitor data.

FEAST Feature Store Example for Scaling Machine Learning
FEAST Feature Store Example- Learn to use FEAST Feature Store to manage, store, and discover features for customer churn prediction machine learning project.

NLP Project for Multi Class Text Classification using BERT Model
In this NLP Project, you will learn how to build a multi-class text classification model using using the pre-trained BERT model.