Sqlalchemy python outer join
This will perform an INNER JOIN using both tables. If you are wondering what is an INNER JOIN ? You can read more about of what a join is and several join methods using SQLAlchemy/Flask and Python in the following section.
SQLAlchemy JOIN Fundamentals
Let’s order only the clients that have a pending order using the next SQL sequence:
1SELECT Clients.name, Orders.order_id FROM Clients 2 INNER JOIN Orders ON Clients.client_id=Orders.order_id 3 ORDER BY Clients.name;
If there are clients that do not have a match with an order, these clients won’t show on the results. The previous code will give us the next results:
| name | order_id |
|---|---|
| Brandon Hughes | 111 |
| Bryan Owens | 222 |
| Bryan Owens | 333 |
| Sheryl Lohan | 444 |
See that Brian Owens appears two times in the results since he has two pending orders (222 and 333). Mark White does not appear since there is no pending order made by him.
The JOIN that we just did is called INNER JOIN and it’s the most common JOIN in SQL. The INNER JOIN selects all rows of both tables as long as there is a match between the columns in both tables. The basic syntax of the INNER JOIN is the following:
1SELECT table1.column1, table2.column2... 2 FROM table1 3 INNER JOIN table2 4 ON table1.common_field = table2.common_field;
Now that we know what a JOIN clause is in SQL, we can move on to exploring how to work with JOINs using SQLAlchemy, Flask, and Python.
SQLAlchemy JOIN Using Flask and Python
The next example will explain how to work with the JOIN clause using SQLAlchemy/Flask and Python, but this example is perfectly adaptable to just SQLAlchemy and Python if desired. Let’s work with the tables from the previous example, we proceed to create the models/tables using SQLAlchemy/Flask and Python as follows:
1from flask import Flask 2 from flask_sqlalchemy import SQLAlchemy 3 4db = SQLAlchemy() 5 6 class Client(db.Model): 7 client_id = db.Column(db.Integer, primary_key=True) 8 name = db.Column(db.String(50)) 9 phone = db.Column(db.Integer) 10 11 class Order(db.Model): 12 order_id = db.Column(db.Integer, primary_key=True) 13 client_id = db.Column(db.String(50), db.ForeignKey('clients.client_id') 14 invoice = db.Column(db.Integer)
With the models/tables ready, we proceed to write our query (syntax) as follows:
1results = db.session.query(table1, table2).join(table2).all()
Following this syntax, let’s now join the Client table with the Order table as follows:
1from flask import Flask 2 from flask_sqlalchemy import SQLAlchemy 3 4db = SQLAlchemy() 5 6 class Client(db.Model): 7 client_id = db.Column(db.Integer, primary_key=True) 8 name = db.Column(db.String(50)) 9 phone = db.Column(db.Integer) 10 11 class Order(db.Model): 12 order_id = db.Column(db.Integer, primary_key=True) 13 client_id = db.Column(db.String(50), db.ForeignKey('clients.client_id') 14 invoice = db.Column(db.Integer) 15 16results = db.session.query(Client, Order).join(Order).all() 17 18 #Printing the results: 19 for client, order in results: 20 print(client.name, order.order_id)
After printing the results and running the code in a bash terminal we get:
1Brandon Hughes 111 2Bryan Owens 222 3Bryan Owens 333 4Sheryl Loan 444
INNER JOIN is not the only way that we can combine tables, sometimes we might want to receive information from matched and unmatched rows from one of the tables or both, the JOIN that will help us on getting also unmatching information is called OUTER JOIN
SQLAlchemy OUTER JOIN Using Flask and Python
There are multiple types of OUTER JOINs , and depending on which table or tables we want to extract information from, we will decide which OUTER JOIN will be the best choice. There are 3 types of OUTER JOINs :
LEFT OUTER JOIN:
Unlike an INNER JOIN , where we only look for matching rows in both tables, a LEFT JOIN will give priority to the table on the left, giving us all of its rows, and will still look for matching rows on the right table. In other words, we’ll get all the information from the left table and only the matching information from the right table.
Let’s use the same tables used in the SQLAlchemy/Flask and Python INNER JOIN example previously:
1from flask import Flask 2 from flask_sqlalchemy import SQLAlchemy 3 4db = SQLAlchemy() 5 6 class Client(db.Model): 7 client_id = db.Column(db.Integer, primary_key=True) 8 name = db.Column(db.String(50)) 9 phone = db.Column(db.Integer) 10 11 class Order(db.Model): 12 order_id = db.Column(db.Integer, primary_key=True) 13 client_id = db.Column(db.String(50), db.ForeignKey('clients.client_id') 14 invoice = db.Column(db.Integer)
Now, in our query, instead of writing the word join , we need to write the word outerjoin which by default is a LEFT OUTER JOIN in SQLAlchemy:
1results = db.session.query(table1, table2).join(table2).all()
1results = db.session.query(table1, table2).outerjoin(table2).all()
We then proceed to write our query and print the results as follows:
1from flask import Flask 2 from flask_sqlalchemy import SQLAlchemy 3 4db = SQLAlchemy() 5 6 class Client(db.Model): 7 client_id = db.Column(db.Integer, primary_key=True) 8 name = db.Column(db.String(50)) 9 phone = db.Column(db.Integer) 10 11 class Order(db.Model): 12 order_id = db.Column(db.Integer, primary_key=True) 13 client_id = db.Column(db.String(50), db.ForeignKey('clients.client_id') 14 invoice = db.Column(db.Integer) 15 16results = db.session.query(Client, Order).outerjoin(Order).all() 17 18 #Printing the results: 19 for client, order in results: 20 print(client.name, order.order_id)
After printing the results and running the code in a bash terminal we get:
1Brandon Hughes 111 2Bryan Owens 222 3Bryan Owens 333 4Sheryl Loan 444 5Mark White Null
See that now Mark White appears on the results with no order, in this way we have all the information from the left table plus the matching information from the right table.
RIGHT OUTER JOIN:
A RIGHT JOIN will give priority to the table on the right, giving us all of its rows, and will still look for matching rows on the left table. In other words, we’ll get all the information from the right table and only the matching information from the left one.
For the next example, let’s add an order that for some reason does not have a client assigned to it, so the tables should look like this:
Using these tables in SQLAlchemy/Flask and Python as follows:
1from flask import Flask 2 from flask_sqlalchemy import SQLAlchemy 3 4db = SQLAlchemy() 5 6 class Client(db.Model): 7 client_id = db.Column(db.Integer, primary_key=True) 8 name = db.Column(db.String(50)) 9 phone = db.Column(db.Integer) 10 11 class Order(db.Model): 12 order_id = db.Column(db.Integer, primary_key=True) 13 client_id = db.Column(db.String(50), db.ForeignKey('clients.client_id') 14 invoice = db.Column(db.Integer)
There is no word in SQLAlchemy that allows us to perform a RIGHT OUTER JOIN since the outerjoin word refers to a LEFT OUTER JOIN by default. If we want to perform a RIGHT OUTER JOIN , we just need to flip the order of our tables, in other words, Table 1 RIGHT OUTER JOIN Table 2 is equivalent of Table 2 LEFT OUTER JOIN Table 1 , so the query should look like this:
1results = db.session.query(table2, table1).outerjoin(table1).all()
We then proceed to write our query and print the results as follows:
1from flask import Flask 2 from flask_sqlalchemy import SQLAlchemy 3 4db = SQLAlchemy() 5 6 class Client(db.Model): 7 client_id = db.Column(db.Integer, primary_key=True) 8 name = db.Column(db.String(50)) 9 phone = db.Column(db.Integer) 10 11 class Order(db.Model): 12 order_id = db.Column(db.Integer, primary_key=True) 13 client_id = db.Column(db.String(50), db.ForeignKey('clients.client_id') 14 invoice = db.Column(db.Integer) 15 16results = db.session.query(Order, Client).outerjoin(Client).all() 17 18 #Printing the results: 19 for client, order in results: 20 print(client.name, order.order_id)
After printing the results and running the code in a bash terminal we get:
1Brandon Hughes 111 2Bryan Owens 222 3Bryan Owens 333 4Sheryl Loan 444 5NULL 555
See that now Order 555 appears on the results with no client assigned to it, in this way we have all the information from the right table plus the matching information from the left table.
FULL OUTER JOIN:
An OUTER JOIN or FULL OUTER JOIN will combine the results of a LEFT JOIN and a RIGHT JOIN , giving us all the rows from the first and second tables. If there is no match in one of the table rows, there will be a NULL on that side of the table’s result.
There is no word in SQLAlchemy that allows us to perform a FULL OUTER JOIN since the outerjoin word refers to a LEFT OUTER JOIN by default. If we want to perform a FULL OUTER JOIN , we just need to add full=True statement in our query as follows:
1results = db.session.query(table2, table1).outerjoin(table1, full=True).all()
We then proceed to write our query and print the results as follows:
1from flask import Flask 2 from flask_sqlalchemy import SQLAlchemy 3 4db = SQLAlchemy() 5 6 class Client(db.Model): 7 client_id = db.Column(db.Integer, primary_key=True) 8 name = db.Column(db.String(50)) 9 phone = db.Column(db.Integer) 10 11 class Order(db.Model): 12 order_id = db.Column(db.Integer, primary_key=True) 13 client_id = db.Column(db.String(50), db.ForeignKey('clients.client_id') 14 invoice = db.Column(db.Integer) 15 16results = db.session.query(Order, Client).outerjoin(Client, full=True).all() 17 18 #Printing the results: 19 for client, order in results: 20 print(client.name, order.order_id)
After printing the results and running the code in a bash terminal we get:
1Brandon Hughes 111 2Bryan Owens 222 3Bryan Owens 333 4Sheryl Loan 444 5NULL 555 6Mark White NULL
As mentioned before, we see that we get all the information from both tables including the unmatched results from table 1 and table 2.