SQLAlchemy JOIN To perform a basic using SQLAlchemy/Flask and Python , you need to write your query as follows: This will perform an using both tables. If you are wondering what is an ? You can read more about of what a is and several methods using SQLAlchemy/Flask and Python in the following section. SQLAlchemy JOIN Fundamentals Throughout our development process, we will find ourselves with the need to consult information belonging to different tables of a database. Something very useful for this is the union of tables to make the consultation of any required information much easier. Here is where the clause comes into action. Before understanding how to work with Python and SQLAlchemy Joins, let's explain the definition of a in SQL. An SQL clause is used to join rows from two or more tables, based on a related column between them (key fields). You can read more about the definition of a here https://www.w3schools.com/sql/sql join.asp. Let's see a basic example of a common between two tables, Clients and Orders , as follows: Clients | client id | name | phone | | | | | | 01 | Bryan Owens | 123 123 4567 | | 02 | Mark White | 123 987 6543 | | 03 | Brandon Hughes | 123 159 1591 | | 04 | Sheryl Lohan | 123 456 4567 | Orders | order id | client id | invoice | | | | | | 111 | 03 | 654 | | 222 | 01 | 159 | | 333 | 01 | 901 | | 444 | 04 | 778 | Let's order only the clients that have a pending order using the next SQL sequence : 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 that we just did is called and it's the most common in SQL. The selects all rows of both tables as long as there is a match between the columns in both tables. The basic syntax of the is the following: Now that we know what a 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 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: With the models/tables ready, we proceed to write our query (syntax) as follows: Following this syntax, let's now join the Client table with the Order table as follows: After printing the results and running the code in a bash terminal we get: 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 that will help us on getting also unmatching information is called SQLAlchemy OUTER JOIN Using Flask and Python There are multiple types of , and depending on which table or tables we want to extract information from, we will decide which will be the best choice. There are 3 types of : LEFT OUTER JOIN: Unlike an , where we only look for matching rows in both tables, a 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 example previously: Now, in our query, instead of writing the word , we need to write the word which by default is a in SQLAlchemy: query: query: We then proceed to write our query and print the results as follows: After printing the results and running the code in a bash terminal we get: 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 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: Clients | client id | name | phone | | | | | | 01 | Bryan Owens | 123 123 4567 | | 02 | Mark White | 123 987 6543 | | 03 | Brandon Hughes | 123 159 1591 | | 04 | Sheryl Lohan | 123 456 4567 | Orders | order id | client id | invoice | | | | | | 111 | 03 | 654 | | 222 | 01 | 159 | | 333 | 01 | 901 | | 444 | 04 | 778 | | 555 | 05 | 001 | Using these tables in SQLAlchemy/Flask and Python as follows: There is no word in SQLAlchemy that allows us to per