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

Savvy Sahai

Data Science Intern, Capgemini
linkedin profile url

As a student looking to break into the field of data engineering and data science, one can get really confused as to which path to take. Very few ways to do it are Google, YouTube, etc. I was one of... Read More

Relevant Projects

Linear Regression Model Project in Python for Beginners Part 1
Machine Learning Linear Regression Project in Python to build a simple linear regression model and master the fundamentals of regression for beginners.

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

Build a Autoregressive and Moving Average Time Series Model
In this time series project, you will learn to build Autoregressive and Moving Average Time Series Models to forecast future readings, optimize performance, and harness the power of predictive analytics for sensor data.

Ensemble Machine Learning Project - All State Insurance Claims Severity Prediction
In this ensemble machine learning project, we will predict what kind of claims an insurance company will get. This is implemented in python using ensemble machine learning algorithms.

Many-to-One LSTM for Sentiment Analysis and Text Generation
In this LSTM Project , you will build develop a sentiment detection model using many-to-one LSTMs for accurate prediction of sentiment labels in airline text reviews. Additionally, we will also train many-to-one LSTMs on 'Alice's Adventures in Wonderland' to generate contextually relevant text.

MLOps Project for a Mask R-CNN on GCP using uWSGI Flask
MLOps on GCP - Solved end-to-end MLOps Project to deploy a Mask RCNN Model for Image Segmentation as a Web Application using uWSGI Flask, Docker, and TensorFlow.

Customer Market Basket Analysis using Apriori and Fpgrowth algorithms
In this data science project, you will learn how to perform market basket analysis with the application of Apriori and FP growth algorithms based on the concept of association rule learning.

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.

Learn to Build a Polynomial Regression Model from Scratch
In this Machine Learning Regression project, you will learn to build a polynomial regression model to predict points scored by the sports team.

Build a Face Recognition System in Python using FaceNet
In this deep learning project, you will build your own face recognition system in Python using OpenCV and FaceNet by extracting features from an image of a person's face.