In the remaining chapter, we have learnt approximately numerous capabilities along with max(), min(), matter(), and many others., right here, we are able to learn about set operations and their uses.
Set operations together with UNION and INTERSECT are supported through preferred SQL and most of its dialect. SQLAlchemy implements them with the help of following functions −
union()
While combining consequences of two or greater SELECT statements, UNION gets rid of duplicates from the resultset. The variety of columns and datatype must be equal in each the tables.
The union() function returns a CompoundSelect item from multiple tables. Following example demonstrates its use −
from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, union
engine = create_engine('sqlite:///college.db', echo = True)
meta = MetaData()
conn = engine.connect()
addresses = Table(
'addresses', meta,
Column('id', Integer, primary_key = True),
Column('st_id', Integer),
Column('postal_add', String),
Column('email_add', String)
)
u = union(addresses.select().where(addresses.c.email_add.like('%@gmail.com addresses.select().where(addresses.c.email_add.like('%@yahoo.com'))))
result = conn.execute(u)
result.fetchall()
The union construct interprets to following SQL expression −
SELECT addresses.id,
addresses.st_id,
addresses.postal_add,
addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? UNION SELECT addresses.id,
addresses.st_id,
addresses.postal_add,
addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ?
From our addresses desk, following rows represent the union operation −
[
(1, 1, 'Shivajinagar Pune', 'ravi@gmail.com'),
(2, 1, 'ChurchGate Mumbai', 'kapoor@gmail.com'),
(3, 3, 'Jubilee Hills Hyderabad', 'komal@gmail.com'),
(4, 5, 'MG Road Bangaluru', 'as@yahoo.com')
]
union_all()
UNION ALL operation cannot take away the duplicates and can't type the records within the resultset. For example, in above query, UNION is changed by using UNION ALL to peer the impact.
u = union_all(addresses.select().where(addresses.c.email_add.like('%@gmail.com')), addresses.select().where(addresses.c.email_add.like('%@yahoo.com')))
The corresponding SQL expression is as follows −
SELECT addresses.id,
addresses.st_id,
addresses.postal_add,
addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? UNION ALL SELECT addresses.id,
addresses.st_id,
addresses.postal_add,
addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ?
except_()
The SQL EXCEPT clause/operator is used to combine SELECT statements and go back rows from the first SELECT statement that aren't back through the second SELECT declaration. The except_() characteristic generates a SELECT expression with EXCEPT clause.
In the subsequent example, the except_() function returns simplest the ones statistics from addresses table that have ‘gmail.Com’ in email_add subject however excludes those that have ‘Pune’ as a part of postal_add area.
u = except_(addresses.select().where(addresses.c.email_add.like('%@gmail.com')), addresses.select().where(addresses.c.postal_add.like('%Pune')))
Result of the above code is the subsequent SQL expression −
SELECT addresses.id,
addresses.st_id,
addresses.postal_add,
addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? EXCEPT SELECT addresses.id,
addresses.st_id,
addresses.postal_add,
addresses.email_add
FROM addresses
WHERE addresses.postal_add LIKE ?
Assuming that addresses table contains records used in in advance examples, it'll show following output −
[(2, 1, 'ChurchGate Mumbai', 'kapoor@gmail.com'),
(3, 3, 'Jubilee Hills Hyderabad', 'komal@gmail.com')]
intersect()
Using INTERSECT operator, SQL shows not unusual rows from both the SELECT statements. The intersect() characteristic implements this behaviour.
In following examples, SELECT constructs are parameters to intersect() function. One returns rows containing ‘gmail.Com’ as a part of email_add column, and different returns rows having ‘Pune’ as a part of postal_add column. The end result could be commonplace rows from each resultsets.
u = intersect(addresses.select().where(addresses.c.email_add.like('%@gmail.com')), addresses.select().where(addresses.c.postal_add.like('%Pune')))
In impact, this is equal to following SQL statement −
SELECT addresses.id,
addresses.st_id,
addresses.postal_add,
addresses.email_add
FROM addresses
WHERE addresses.email_add LIKE ? INTERSECT SELECT addresses.id,
addresses.st_id,
addresses.postal_add,
addresses.email_add
FROM addresses
WHERE addresses.postal_add LIKE ?
The sure parameters ‘%gmail.Com’ and ‘%Pune’ generate a unmarried row from unique records in addresses table as proven under −
[(1, 1, 'Shivajinagar Pune', 'ravi@gmail.com')]