Back to articles list September 25, 2020 - 5 minutes read SQL JOIN Cheat Sheet LearnSQL.com Team Tags: cheat sheet joins Download this 2-page SQL JOIN Cheat Sheet in PDF or PNG format, print it out, and stick to your desk. The SQL JOIN Cheat Sheet provides you with the syntax of different JOINs, and examples. You can download this cheat sheet as follows: Download 2-page SQL JOIN Cheat Sheet in PDF format (A4) Download 2-page SQL JOIN Cheat Sheet in PDF format (Letter) Download 1-page SQL JOIN Cheat Sheet in PDF format (A3) Download 1-page SQL JOIN Cheat Sheet in PDF format (Ledger) SQL JOINs Cheat Sheet JOINING TABLES JOIN combines data from two tables. JOIN typically combines rows with equal values for the specified columns. Usually, one table contains a primary key, which is a column or columns that uniquely identify rows in the table (the cat_id column in the cat table). The other table has a column or columns that refer to the primary key columns in the first table (the cat_id column in the toy table). Such columns are foreign keys. The JOIN condition is the equality between the primary key columns in one table and columns referring to them in the other table. JOIN JOIN returns all rows that match the ON condition. JOIN is also called INNER JOIN SELECT * FROM toy JOIN cat ON toy.cat_id = cat.cat_id; There is also another, older syntax, but it isn't recommended. List joined tables in the FROM clause, and place the conditions in the WHERE clause. SELECT * FROM toy, cat WHERE toy.cat_id = cat.cat_id; JOIN CONDITIONS The JOIN condition doesn't have to be an equality – it can be any condition you want. JOIN doesn't interpret the JOIN condition, it only checks if the rows satisfy the given condition. To refer to a column in the JOIN query, you have to use the full column name: first the table name, then a dot (.) and the column name: ON cat.cat_id = toy.cat_id You can omit the table name and use just the column name if the name of the column is unique within all columns in the joined tables. NATURAL JOIN If the tables have columns with the same name, you can use NATURAL JOIN instead of JOIN. SELECT * FROM toy NATURAL JOIN cat; The common column appears only once in the result table. Note: NATURAL JOIN is rarely used in real life. LEFT JOIN LEFT JOIN returns all rows from the left table with matching rows from the right table. Rows without a match are filled with NULLs. LEFT JOIN is also called LEFT OUTER JOIN. SELECT * FROM toy LEFT JOIN cat ON toy.cat_id = cat.cat_id; RIGHT JOIN RIGHT JOIN returns all rows from the right table with matching rows from the left table. Rows without a match are filled with NULLs. RIGHT JOIN is also called RIGHT OUTER JOIN. SELECT * FROM toy RIGHT JOIN cat ON toy.cat_id = cat.cat_id; FULL JOIN FULL JOIN returns all rows from the left table and all rows from the right table. It fills the non-matching rows with NULLs. FULL JOIN is also called FULL OUTER JOIN. SELECT * FROM toy FULL JOIN cat ON toy.cat_id = cat.cat_id; CROSS JOIN CROSS JOIN returns all possible combinations of rows from the left and right tables. SELECT * FROM toy CROSS JOIN cat; Other syntax: SELECT * FROM toy, cat; COLUMN AND TABLE ALIASES Aliases give a temporary name to a table or a column in a table. A column alias renames a column in the result. A table alias renames a table within the query. If you define a table alias, you must use it instead of the table name everywhere in the query. The AS keyword is optional in defining aliases. SELECT o.name AS owner_name, c.cat_name FROM cat AS c JOIN owner AS o ON c.owner_id = o.id; SELF JOIN You can join a table to itself, for example, to show a parent-child relationship. Each occurrence of the table must be given a different alias. Each column reference must be preceded with an appropriate table alias. SELECT child.cat_name AS child_name, mom.cat_name AS mom_name FROM cat AS child JOIN cat AS mom ON child.mom_id = mom.cat_id; NON-EQUI SELF JOIN You can use a non-equality in the ON condition, for example, to show all different pairs of rows. SELECT a.toy_name AS toy_a, b.toy_name AS toy_b FROM toy a JOIN toy b ON a.cat_id < b.cat_id; MULTIPLE JOINS You can join more than two tables together. First, two tables are joined, then the third table is joined to the result of the previous joining. JOIN & JOIN SELECT t.toy_name, c.cat_name, o.name AS owner_name FROM toy t JOIN cat c ON t.cat_id = c.cat_id JOIN owner o ON c.owner_id = o.id; JOIN & LEFT JOIN SELECT t.toy_name, c.cat_name, o.name AS owner_name FROM toy t JOIN cat c ON t.cat_id = c.cat_id LEFT JOIN owner o ON c.owner_id = o.id; LEFT JOIN & LEFT JOIN SELECT t.toy_name, c.cat_name, o.name AS owner_name FROM toy t LEFT JOIN cat c ON t.cat_id = c.cat_id LEFT JOIN owner o ON c.owner_id = o.id; JOIN WITH MULTIPLE CONDITIONS You can use multiple JOIN conditions using the ON keyword once and the AND keywords as many times as you need. SELECT cat_name, o.name AS owner_name, c.age AS cat_age, o.age AS owner_age FROM cat c JOIN owner o ON c.owner_id = o.id AND c.age < o.age; Try out the interactive SQL JOINs course at LearnSQL.com, and check out our other SQL courses. Tags: cheat sheet joins You may also like SQL Basics Cheat Sheet This 2-page SQL Basics Cheat Sheet will be a great value for beginners as well as for professionals. Download it in PDF or PNG format. Read more SQL Window Functions Cheat Sheet This 2-page SQL Window Functions Cheat Sheet covers the syntax of window functions and a list of window functions. Download it in PDF or PNG format. Read more What Is Vertabelo’s SQL Cheat Sheet? Rock the SQL! You don’t have to be a programmer to master SQL. Download the SQL Cheat Sheet and find quick answers for the common problems with SQL queries. Read more Can you Join two Tables Without a Common Column? Do you need to combine two tables without a common column? Learn how the CROSS JOIN and the UNION operators can help you with this task. Read more What's the Difference Between Having Multiple Tables in FROM and Using JOIN? What’s your approach to SQL JOINs? Do you use JOIN or simply list tables in FROM? Learn why SQL practitioners prefer the new syntax with the JOIN keyword. Read more Practical Examples of When to Use Non-Equi JOINs in SQL Do you only use equals in your JOIN conditions? Learn how non-equi JOINs can assist with listing duplicates and joining tables based on a range of values. Read more How to Learn SQL JOINs Learn how to use SQL JOINs to effectively combine data across multiple tables and analyze sophisticated data sets. Read more How to Join 3 Tables (or More) in SQL Have you ever wondered how to join three tables in SQL? It's easy when you know the basics. Joining three tables can be as easy as joining two tables. Read more Subquery vs. JOIN What are the differences between a subquery and a JOIN in SQL? How do you choose one over the other? See examples of when to use which. Read more Subscribe to our newsletter Join our weekly newsletter to be notified about the latest posts.