Set Operations in Python

Set operations are a set of SQL commands that allow you to combine the results of two or more SELECT statements. The three most common set operations are UNION, INTERSECT, and EXCEPT.

• UNION: It combines the results of two or more SELECT statements and returns only the unique rows.
• INTERSECT: It returns only the rows that are common to both SELECT statements.
• EXCEPT: It returns only the rows that are unique to the first SELECT statement.

These operations can be used to filter and combine data from multiple tables or to retrieve specific data based on certain conditions. They are useful in situations where you need to retrieve data from multiple tables or when you want to filter data based on certain criteria.

UNION

One of the most common set operations is the UNION operation, which combines the results of two or more SELECT statements and returns only the unique rows. For example, if we wanted to find all the employees who are older than 25 OR are in the IT department, we may use the following query:

Example

This query would return a list of all employees who are older than 25 OR are in the IT department.

SELECT * FROM employees
WHERE age > 25
UNION
SELECT * FROM employees
WHERE department = 'IT'

INTERSECT

Another useful set operation is the INTERSECT operation, which returns only the rows that are common to both SELECT statements. For example, if we wanted to find all employees who are older than 25 AND are in the IT department, we may use the following query:

Example

This query would return a list of all employees who are older than 25 AND are in the IT department.

SELECT * FROM employees
WHERE age > 25
INTERSECT
SELECT * FROM employees
WHERE department = 'IT'

EXCEPT

Finally, the EXCEPT operation returns only the rows that are unique to the first SELECT statement. For example, if we wanted to find all employees who are older than 25 but are NOT in the IT department, we may use the following query.

Example

This query would return a list of all employees who are older than 25 but are NOT in the IT department.

SELECT * FROM employees
WHERE age > 25
EXCEPT
SELECT * FROM employees
WHERE department = 'IT'

SQLAlchemy Core is a powerful tool for working with databases in Python. One of the key features of SQLAlchemy Core is its support for set operations, which allow you to perform complex queries on your data. In this article, we will explore the basics of set operations in SQLAlchemy Core and provide some examples to help you understand how to use them effectively.