---

In this chapter, we can learn how to use Joins in SQLAlchemy.

Effect of joining is done through just putting two tables in both the columns clause or the where clause of the select() construct. Now we use the be part of() and outerjoin() strategies.

The be part of() method returns a join item from one table item to some other.

join(right, onclause = None, isouter = False, full = False)

The capabilities of the parameters cited inside the above code are as follows −

• right − the proper aspect of the join; that is any Table item
• onclause − a SQL expression representing the ON clause of the be part of. If left at None, it tries to enroll in the 2 tables based totally on a overseas key dating
• isouter − if True, renders a LEFT OUTER JOIN, rather than JOIN
• complete − if True, renders a FULL OUTER JOIN, in place of LEFT OUTER JOIN

For instance, following use of be a part of() approach will automatically bring about join primarily based on the overseas key.

>>> print(students.join(addresses))

This is equal to following SQL expression −

students JOIN addresses ON students.id = addresses.st_id

You can explicitly point out joining criteria as follows −

j = students.join(addresses, students.c.id == addresses.c.st_id)

If we now construct the beneath select construct the usage of this join as −

stmt = select([students]).select_from(j)

This will bring about following SQL expression −

SELECT students.id, students.name, students.lastname
FROM students JOIN addresses ON students.id = addresses.st_id

If this declaration is achieved the use of the connection representing engine, data belonging to selected columns can be displayed. The entire code is as follows −

from sqlalchemy import create_engine, MetaData, Table, Column, Integer, String, ForeignKey
engine = create_engine('sqlite:///college.db', echo = True)

meta = MetaData()
conn = engine.connect()
students = Table(
   'students', meta, 
   Column('id', Integer, primary_key = True), 
   Column('name', String), 
   Column('lastname', String), 
)

addresses = Table(
   'addresses', meta, 
   Column('id', Integer, primary_key = True), 
   Column('st_id', Integer,ForeignKey('students.id')), 
   Column('postal_add', String), 
   Column('email_add', String)
)

from sqlalchemy import join
from sqlalchemy.sql import select
j = students.join(addresses, students.c.id == addresses.c.st_id)
stmt = select([students]).select_from(j)
result = conn.execute(stmt)
result.fetchall()

The following is the output of the above code −

[
   (1, 'Ravi', 'Kapoor'),
   (1, 'Ravi', 'Kapoor'),
   (3, 'Komal', 'Bhandari'),
   (5, 'Priya', 'Rajhans'),
   (2, 'Rajiv', 'Khanna')
]