Simple JOIN examples. MySQL JOIN: description, example of using the command and recommendations Mysql update left join examples
In MySQL, selecting using JOIN can be done in different ways. We will try to consider all these types of requests. Here is a list of all queries involving JOIN:
And here is an illustration of these types of JOIN:
I will attach files from our site to the article, among which there will be join.php in which I will display all records using different JOIN operators.
INNER JOINWe'll start with this INNER JOIN operator, because this operator fires by default if you simply write JOIN in the query. This operator selects all records from two tables where the condition following the ON operator is satisfied. We have two tables Files and Messages:
Messages table:
The query with JOIN will be as follows:
SELECT * FROM Messages INNER JOIN Files ON Messages.fid=Files.fid
As a result, the following records will be displayed:
Files table:
LEFT JOIN will be needed when we display all message records, and whether or not there is an attached file, we will check through PHP.
LEFT JOIN without intersections with the right tableLEFT JOIN returns all records from the left table, except those whose fid matches in the right table.
Messages table:
A query with a LEFT JOIN without intersections would be:
SELECT * FROM Messages LEFT JOIN Files ON Messages.fid=Files.fid WHERE Files.fid IS NULL
As a result, we get the following selection:
Files table:
RIGHT JOIN will be needed when we display all attached files, no matter if they are used or not, just all files.
RIGHT JOIN without intersectionsRIGHT JOIN without intersections displays all records from the right table, except those that have intersections with the left table.
Messages table:
A query with a RIGHT JOIN without intersections would be:
SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid=Files.fid WHERE Messages.fid IS NULL
This way we will get the following data:
| mid | bodytext | fid | path |
| NULL | NULL | 1 | /files/1.png |
RIGHT JOIN will be needed when displaying all attached files that are not attached to any messages. For example, if we want to display files that are not in use.
FULL OUTER JOINAlthough SQL has a FULL OUTER JOIN operator, MySQL does not have this operator. The fact is that such an operator is a huge load on the server. Now we have 3 files and 3 messages, while 4 lines are formed as a result of the request. I'm not sure if it's a good idea to write a query that combines two queries, LEFT JOIN and RIGHT JOIN. But it is still possible to emulate a FULL OUTER JOIN request.
Messages table:
Emulating a request with FULL OUTER JOIN would be as follows:
SELECT * FROM Messages LEFT JOIN Files ON Messages.fid = Files.fid UNION SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid = Files.fid
In this query, we are using UNION operator to join two queries LEFT JOIN and RIGHT JOIN.
As a result, we will receive the following records:
| mid | bodytext | fid | path |
| 1 | Test | 2 | /files/2.png |
| 2 | Hi | NULL | NULL |
| 3 | Hello | 3 | /files/3.png |
| NULL | NULL | 1 | /files/1.png |
And here I find it difficult to say why a FULL OUTER JOIN is required. But since it is in SQL, it will probably be needed later.
FULL OUTER JOIN without intersectionsAnother type of JOIN is even crazier than just FULL OUTER JOIN, namely FULL OUTER JOIN without intersections. I can't even suggest where this type of JOIN can be used. Because as a result we receive files that are not used and messages without files. And as you probably already guessed, this operator is also not in MySQL. All that remains is to emulate it using two operators: LEFT JOIN without enumerations and RIGHT JOIN without intersections.
Emulating a FULL OUTER JOIN request without intersections:
$sql = "SELECT * FROM Messages LEFT JOIN Files ON Messages.fid = Files.fid WHERE Files.fid IS NULL UNION SELECT * FROM Messages RIGHT JOIN Files ON Messages.fid = Files.fid WHERE Messages.fid IS NULL";
As a result (the source tables are the same as in the example with FULL OUTER JOIN) we get:
| mid | bodytext | fid | path |
| 2 | Hi | NULL | NULL |
| NULL | NULL | 1 | /files/1.png |
That's probably all, in the next lessons we will start writing even more complex queries to several tables at once.
9.5KIn this article, we will cover the MySQL LEFT JOIN operator and how to use it to query data from two or more database tables.
Introduction to LEFT JOIN MySQLThe MySQL LEFT JOIN operator allows you to query data from two or more database tables. It is an optional part of the SELECT statement that appears after FROM.
Let's assume that you are going to query data from tables t1 and t2. Using the example query below, we illustrate the LEFT JOIN syntax by joining two tables:
SELECT t1.c1, t1.c2, t2.c1, t2.c2 FROM t1 LEFT JOIN t2 ON t1.c1 = t2.c1;
When you join to tables t1 and t2 using the LEFT JOIN operator, if a row from the left table t1 matches a row from the right table t2 based on the join condition (t1.c1 = t2.c1), that row will be included in the result set.
If the value in the left table does not match the value in the right table, the row in the left table is also selected and combined with the "conditional" row from the right table. The "conditional" row contains NULL for all corresponding columns in the SELECT statement.
In other words, the MySQL LEFT JOIN condition allows you to select rows from both tables that match, plus all rows from the left table (t1) even without matching rows from the right table (t2).
The following diagram will help you understand how the LEFT JOIN condition works. The intersection of the two circles are the rows that match in both tables, and the remainder of the left circle is the rows in table t1 that do not have a corresponding row in table t2. Therefore, all rows in the left table are included in the result set.
Note that the returned values must also match the conditions in the WHERE and HAVING clauses, if those clauses are available in the query. Examples of using LEFT JOIN Using the MySQL LEFT JOIN operator to join two tables
Let's take two tables of customers and orders from the demo database:
In the database described in the diagram:
- Each order in the orders table must belong to a customer in the customers table;
- Each customer in the customers table can have zero or more orders in the orders table.
To find the orders belonging to each customer, you can use the MySQL LEFT JOIN example:
SELECT c.customerNumber, c.customerName, orderNumber, o.status FROM customers c LEFT JOIN orders o ON c.customerNumber = o.customerNumber
View example
The left table is clients, so all clients are included in the result set. But it has rows that have customer data but no order data, for example. 168, 169, etc. The order data in these rows is NULL. This means that these customers do not have orders in the corresponding table.
Since we used the same column name (orderNumber) to join the two tables, we can make the query shorter using the syntax below:
SELECT c.customerNumber, customerName, orderNumber, status FROM customers c LEFT JOIN orders USING (customerNumber);
If you replace the MySQL SELECT LEFT JOIN statement with an INNER JOIN statement, you will only get customers who have placed at least one order.
Using the MySQL LEFT JOIN operator to find non-matching rowsThe LEFT JOIN operator can be useful if you want to find rows in the left table that do not match rows in the right one. To find rows that don't match between two tables, you need to add a WHERE clause to the SELECT to query only the rows whose column values in the right table contain NULL .
For example, to find all customers who have not placed any orders, use the following query:
View example
Condition in a WHERE clause or condition in an ON clauseConsider the following example using MySQL LEFT JOIN WHERE :
In this example, we used the LEFT JOIN operator to query data from the orders and orderDetails tables. The request returns the order and its details, if any, for order 10123.
The SQL JOIN operator is designed to join two or more database tables based on matching conditions. This operator exists only in relational databases. It is thanks to JOIN that relational databases have such powerful functionality that allows not only data storage, but also their, at least the simplest, analysis using queries. Let's look at the main nuances of writing SQL queries with the JOIN operator, which are common to all DBMSs (database management systems). To join two tables, the SQL JOIN operator has the following syntax:
SELECT COLUMN_NAMES (1..N) FROM TABLE_NAME_1 JOIN TABLE_NAME_2 ON CONDITION
One or more links with a JOIN operator may be followed by an optional WHERE or HAVING section, in which, just like in a simple SELECT query, the selection condition is specified. Common to all DBMSs is that in this construction, instead of JOIN, INNER JOIN, LEFT OUTER JOIN, RIGHT OUTER JOIN, FULL OUTER JOIN, CROSS JOIN (or, alternatively, a comma) can be specified.
INNER JOINA query with the INNER JOIN operator is designed to join tables and display the resulting table in which the data completely intersects according to the condition specified after ON.
A simple JOIN does the same thing. Thus, the word INNER is optional.
Example 1. There is a database of an advertisement portal. It contains a table of Categories (categories of advertisements) and Parts (parts, or otherwise - headings, which belong to categories). For example, the parts Apartments and Cottages belong to the Real Estate category, and the parts Cars and Motorcycles belong to the Transport category. These tables with filled data look like this:
Parts table:
Note that in the Parts table, Books have a Cat - a link to a category, which is not in the Categories table, and in the Categories table, Equipment has a Cat_ID - a primary key, a link to which is not in the Parts table. It is required to combine the data from these two tables so that the resulting table contains the Part, Cat and Price fields and so that the data completely overlaps according to the condition. The condition is a match between the category identifier in the Categories table and the link to the category in the Parts table. To do this, write the following request:
SELECT PARTS.Part, CATEGORIES.Cat_ID AS Cat, CATEGORIES.Price FROM PARTS INNER JOIN CATEGORIES ON PARTS.Cat = CATEGORIES.Cat_ID
| Part | Cat | Price |
| Apartments | 505 | 210,00 |
| Cars | 205 | 160,00 |
| Boards | 10 | 105,00 |
| Cabinets | 30 | 77,00 |
There are no Books in the resulting table because this record references a category that is not in the Categories table, and Equipment because this record has a foreign key in the Categories table that is not referenced in the Parts table.
In some cases, when joining tables, you can create less cumbersome queries using the EXISTS predicate and without using JOIN.
There is a database "Theater". The Play table contains data about productions. Team table - about the roles of the actors. The Actor table is about actors. Director table - about directors. Table fields, primary and foreign keys can be seen in the figure below (left-click to enlarge).
Example 3. Display a list of actors who play more than one role in one performance and the number of their roles.
Use the JOIN operator 1 time. Use HAVING, GROUP BY .
Clue. The HAVING operator applies to the number of roles counted by the COUNT aggregate function.
LEFT OUTER JOINA query with the LEFT OUTER JOIN operator is designed to join tables and display a resulting table in which the data is completely intersected by the condition specified after ON, and is supplemented by records from the first (left) table, even if they do not meet the condition. Records in the left table that do not meet the condition will have a column value from the right table that is NULL (undefined).
Example 4. The database and tables are the same as in example 1.
To obtain a resulting table in which data from two tables completely intersect by condition and are supplemented with all data from the Parts table that does not meet the condition, write the following query:
SELECT PARTS.Part, CATEGORIES.Cat_ID AS Cat, CATEGORIES.Price FROM PARTS LEFT OUTER JOIN CATEGORIES ON PARTS.Cat = CATEGORIES.Cat_ID
The result of the query will be the following table:
| Part | Cat | Price |
| Apartments | 505 | 210,00 |
| Cars | 205 | 160,00 |
| Boards | 10 | 105,00 |
| Cabinets | 30 | 77,00 |
| Books | 160 | NULL |
In the resulting table, unlike the table from example 1, there are Books, but the value of the Price column is NULL, since this record has a category identifier that is not in the Categories table.
RIGHT OUTER JOINA query with the RIGHT OUTER JOIN operator is designed to join tables and display a resulting table in which the data is completely intersected by the condition specified after ON, and is supplemented by records from the second (right) table, even if they do not meet the condition. Records in the right table that do not meet the condition will have a column value from the left table that is NULL (undefined).
Example 5.
To obtain the resulting table, in which the data from two tables completely intersect according to the condition and are supplemented with all the data from the Categories table that does not meet the condition, write the following query:
SELECT PARTS.Part, CATEGORIES.Cat_ID AS Cat, CATEGORIES.Price FROM PARTS RIGHT OUTER JOIN CATEGORIES ON PARTS.Cat = CATEGORIES.Cat_ID
The result of the query will be the following table:
| Part | Cat | Price |
| Apartments | 505 | 210,00 |
| Cars | 205 | 160,00 |
| Boards | 10 | 105,00 |
| Cabinets | 30 | 77,00 |
| NULL | 45 | 65,00 |
In the resulting table, unlike the table from example 1, there is a record with category 45 and price 65.00, but the Part column value is NULL, since this record has a category identifier that is not referenced in the Parts table .
FULL OUTER JOIN (full outer join)A query with the FULL OUTER JOIN operator is designed to join tables and display a resulting table in which the data is completely intersected by the condition specified after ON, and is supplemented by records from the first (left) and second (right) tables, even if they do not meet the condition. Records that do not meet the condition will have columns from the other table that are NULL (undefined).
Example 6. The database and tables are the same as in the previous examples.
To obtain the resulting table, in which the data from the two tables completely intersect by condition and are supplemented with all the data from both the Parts table and the Categories table that do not meet the condition, write the following query:
SELECT PARTS.Part, CATEGORIES.Cat_ID AS Cat, CATEGORIES.Price FROM PARTS FULL OUTER JOIN CATEGORIES ON PARTS.Cat = CATEGORIES.Cat_ID
The result of the query will be the following table:
| Part | Cat | Price |
| Apartments | 505 | 210,00 |
| Cars | 205 | 160,00 |
| Boards | 10 | 105,00 |
| Cabinets | 30 | 77,00 |
| Books | 160 | NULL |
| NULL | 45 | 65,00 |
The resulting table contains records of Books (from the left table) and with category 45 (from the right table), and the first of them has an undefined price (column from the right table), and the second has an undefined part (column from the left table).
Aliases for joined tablesIn previous queries, we specified the full names of these tables with the names of the extracted columns from different tables. Such queries look cumbersome: the same word is repeated several times. Is it possible to somehow simplify the design? It turns out that it is possible. To do this, you should use table aliases - their abbreviated names. A nickname can also consist of one letter. Any number of letters in an alias is possible, the main thing is that the request after abbreviation is understandable to you. The general rule is that in the join section of the query, that is, around the word JOIN, you must specify the full table names, and each name must be followed by a table alias.
Example 7: Rewrite the query from Example 1 using aliases for the joined tables.
The request will be as follows:
SELECT P.Part, C.Cat_ID AS Cat, C.Price FROM PARTS P INNER JOIN CATEGORIES C ON P.Cat = C.Cat_ID
The query will return the same thing as the query in example 1, but it is much more compact.
JOIN and joining more than two tablesRelational databases must comply with the requirements of data integrity and non-redundancy, and therefore data about one business process can be contained not only in one, two, but also in three or more tables. In these cases, chains of connected tables are used to analyze data: for example, one (first) table contains a certain quantitative indicator, the second table is connected to the first and third by foreign keys - the data intersects, but only the third table contains a condition, depending on which it may be the quantitative indicator from the first table is derived. And there may be even more tables. Using the SQL JOIN operator, you can join a large number of tables in a single query. In such queries, one join section is followed by another, and each subsequent JOIN joins to the next table the table that was the second in the previous link in the chain. Thus, the SQL query syntax for joining more than two tables is as follows:
SELECT COLUMN_NAMES (1..N) FROM TABLE_NAME_1 JOIN TABLE_NAME_2 ON CONDITION JOIN TABLE_NAME_3 ON CONDITION... JOIN TABLE_NAME_M ON CONDITION
Example 8. The database is the same as in the previous examples. In this example, the Ads table will be added to the Categories and Parts tables, containing data about advertisements published on the portal. Here is a fragment of the Ads table, in which among the records there are records of those advertisements whose publication period expires on 04/02/2018.
| A_Id | Part_ID | Date_start | Date_end | Text |
| 21 | 1 | "2018-02-11" | "2018-04-20" | "I'm selling..." |
| 22 | 1 | "2018-02-11" | "2018-05-12" | "I'm selling..." |
| ... | ... | ... | ... | ... |
| 27 | 1 | "2018-02-11" | "2018-04-02" | "I'm selling..." |
| 28 | 2 | "2018-02-11" | "2018-04-21" | "I'm selling..." |
| 29 | 2 | "2018-02-11" | "2018-04-02" | "I'm selling..." |
| 30 | 3 | "2018-02-11" | "2018-04-22" | "I'm selling..." |
| 31 | 4 | "2018-02-11" | "2018-05-02" | "I'm selling..." |
| 32 | 4 | "2018-02-11" | "2018-04-13" | "I'm selling..." |
| 33 | 3 | "2018-02-11" | "2018-04-12" | "I'm selling..." |
| 34 | 4 | "2018-02-11" | "2018-04-23" | "I'm selling..." |
Let's imagine that today is "2018-04-02", that is, this value is taken by the CURDATE() function - the current date. You want to know which categories the advertisements whose publication deadline is today belong to. Category names are only in the CATEGORIES table, and ad expiration dates are only in the ADS table. In the PARTS table - parts of categories (or more simply, subcategories) of published advertisements. But the PARTS table is linked by the foreign key Cat_ID to the CATEGORIES table, and the ADS table is linked by the foreign key Part_ID to the PARTS table. Therefore, we connect three tables in one query and this query can be called a chain with maximum correctness.
The request will be as follows:
The result of the query is a table containing the names of two categories - “Real Estate” and “Transport”:
| Cat_name |
| Real estate |
| Transport |
Using the SQL CROSS JOIN operator in its simplest form—without a join condition—implements the Cartesian product operation in relational algebra. The result of such a join will be the concatenation of each row of the first table with each row of the second table. Tables can be written in a query either through a CROSS JOIN operator or separated by a comma.
Example 9. The database is still the same, the tables are Categories and Parts. Implement the Cartesian product operation of these two tables.
The request will be as follows:
SELECT (*) Categories CROSS JOIN Parts
Or without explicitly specifying CROSS JOIN - separated by commas:
SELECT (*) Categories, Parts
The query will return a table of 5 * 5 = 25 rows, a fragment of which is given below:
| Cat_ID | Cat_name | Price | Part_ID | Part | Cat |
| 10 | Construction materials | 105,00 | 1 | Apartments | 505 |
| 10 | Construction materials | 105,00 | 2 | Cars | 205 |
| 10 | Construction materials | 105,00 | 3 | Boards | 10 |
| 10 | Construction materials | 105,00 | 4 | Cabinets | 30 |
| 10 | Construction materials | 105,00 | 5 | Books | 160 |
| ... | ... | ... | ... | ... | ... |
| 45 | Technique | 65,00 | 1 | Apartments | 505 |
| 45 | Technique | 65,00 | 2 | Cars | 205 |
| 45 | Technique | 65,00 | 3 | Boards | 10 |
| 45 | Technique | 65,00 | 4 | Cabinets | 30 |
| 45 | Technique | 65,00 | 5 | Books | 160 |
As can be seen from the example, if the result of such a request has any value, then it is perhaps a visual value in some cases when there is no need to display structured information, especially even the simplest analytical sample. By the way, you can specify the columns to display from each table, but even then the information value of such a query will not increase.
But for CROSS JOIN you can set a join condition! The result will be completely different. When using the comma operator instead of explicitly specifying CROSS JOIN, the join condition is specified not by the ON word, but by the WHERE word.
Example 10. The same advertisement portal database, Categories and Parts tables. Using a cross join, connect tables so that the data completely overlaps according to the condition. The condition is a match between the category identifier in the Categories table and the link to the category in the Parts table.
The request will be as follows:
The query will return the same as the query in example 1:
| Part | Cat | Price |
| Apartments | 505 | 210,00 |
| Cars | 205 | 160,00 |
| Boards | 10 | 105,00 |
| Cabinets | 30 | 77,00 |
And this coincidence is not accidental. A query with a cross join according to the join condition is completely similar to a query with an inner join - INNER JOIN - or, given that the word INNER is optional, just JOIN.
Thus, which query variant to use is a matter of style or even habit of the database specialist. Perhaps a cross join with a condition on two tables can be more compact. But the benefit of a cross join for more than two tables (this is also possible) is highly debatable. In this case, the WHERE conditions of the intersection are listed through the AND word. This design can be cumbersome and difficult to read if there is also a WHERE clause at the end of the query with fetch conditions.
Relational Databases and SQL Language
MySQL supports the following JOIN statement syntax when used in SELECT statements:
Table_reference, table_reference table_reference JOIN table_reference table_reference INNER JOIN table_reference join_condition table_reference STRAIGHT_JOIN table_reference table_reference LEFT JOIN table_reference join_condition table_reference LEFT JOIN table_reference table_reference NATURAL ] JOIN table_reference ( oj table_reference LEFT OUTER JOIN table_reference ON conditional_exp r ) table_reference RIGHT JOIN table_reference join_condition table_reference RIGHT JOIN table_reference table_reference NATURAL ] JOIN table_reference
where table_reference is defined as:
Table_name[alias]
and join_condition is defined as:
ON conditional_expr | USING (column_list)
You should never specify any conditions in the ON clause that impose restrictions on the rows in the result set. If you need to specify which rows should be present in the result, you should do so in the WHERE clause.
Please note that in versions prior to 3.23.17, the INNER JOIN operator does not accept the join_condition parameter!
The presence of the last of the above constructions of the LEFT OUTER JOIN expression is due only to ODBC compatibility requirements:
- Instead of a table reference, an alias can be used, which is assigned using the expressions tbl_name AS alias_name or tbl_name alias_name: mysql> SELECT t1.name, t2.salary FROM employee AS t1, info AS t2 WHERE t1.name = t2.name;
- An ON conditional statement is a condition in any form that can be used in a WHERE clause.
- If an entry for the right table is not found in the ON or USING parts of a LEFT JOIN, then a row with all columns set to NULL is used for that table. This feature can be used to find results in a table that has no equivalent in another table: mysql> SELECT table1.* FROM table1 LEFT JOIN table2 ON table1.id=table2.id WHERE table2.id IS NULL; This example finds all rows in table1 with an id value that is not present in table2 (that is, all rows in table1 for which there are no corresponding rows in table2). Of course, this assumes that table2.id is declared as NOT NULL . See section 5.2.6 How MySQL Optimizes LEFT JOIN and RIGHT JOIN .
- USING(column_list) is used to specify a list of columns that must exist in both tables. A USING expression such as: A LEFT JOIN B USING (C1,C2,C3,...) is semantically identical to an ON expression, for example: A.C1=B.C1 AND A.C2=B.C2 AND A.C3=B .C3,...
- A NATURAL JOIN between two tables is defined to be the semantic equivalent of an INNER JOIN or LEFT JOIN with a USING clause that specifies all the columns present in both tables.
- INNER JOIN and (comma) are semantic equivalents. Both perform a full join on the tables used. The way tables are linked is usually specified in the WHERE clause.
- RIGHT JOIN works similarly to LEFT JOIN. To maintain code portability between different databases, it is recommended to use LEFT JOIN instead of RIGHT JOIN.
- STRAIGHT_JOIN is identical to JOIN, except that the left table is always read before the right one. This expression can be used for those (few) cases where the join optimizer puts tables in the wrong order.
- Beginning with MySQL version 3.23.12, you can instruct MySQL which index should be used when retrieving information from a table. This feature is useful if the EXPLAIN statement (which displays information about the structure and order of the SELECT query) indicates that MySQL is using the wrong index. By specifying an index value in USE INDEX (key_list) , you can force MySQL to use only one of the specified indexes to search for a record. An alternative statement, IGNORE INDEX (key_list), prevents MySQL from using that particular index. The expressions USE/IGNORE KEY are synonyms for USE/IGNORE INDEX .
A few examples:
Mysql> SELECT * FROM table1,table2 WHERE table1.id=table2.id; mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id; mysql> SELECT * FROM table1 LEFT JOIN table2 USING (id); mysql> SELECT * FROM table1 LEFT JOIN table2 ON table1.id=table2.id LEFT JOIN table3 ON table2.id=table3.id; mysql> SELECT * FROM table1 USE INDEX (key1,key2) WHERE key1=1 AND key2=2 AND key3=3; mysql> SELECT * FROM table1 IGNORE INDEX (key3) WHERE key1=1 AND key2=2 AND key3=3;
For quite some time, at the beginning of my career as a web developer, I worked with this database as best I could, but I didn’t know much. He composed simple primitive queries, and sometimes even inserted queries into loops. At that time, unfortunately, I did not get my hands on the right book on MySQL and had to learn by trial and error. Many articles on the Internet somehow did not immediately convey to me the wonderful MySQL query - JOIN.
In this publication, I will talk about all the possible options for working with JOIN and, moreover, I will present the principle of operation of each command - visually.
Separately, it is worth noting points 5, 6 and 7. In fact, these queries do not connect two tables, but rather exclude columns from one table that are present in the other. In fact, this can be very useful.
Inner JOIN 
One of the most common requests, occurs extremely often. This query will return all records from the left table (Table A) and records from (Table B), but will only return matching columns.
Example request:
View code SQL
| SELECT< select_list>FROM Table_A A INNER JOIN Table_B B ON A. Key = B. Key |
Left JOIN 
This query will return all columns from the left table (Table A), as well as all columns from the right table (Table B), but only those that match the columns from the left table.
Example request:
View code SQL
| SELECT< select_list>FROM Table_A A LEFT JOIN Table_B B ON A. Key = B. Key |
Right JOIN 
Similar to the previous query, but will return all columns from the right table (Table B), as well as all columns from the left table (Table A) that match the columns from the right table.
Example request:
View code SQL
| SELECT< select_list>FROM Table_A A RIGHT JOIN Table_B B ON A. Key = B. Key |
Outer JOIN 
Often this query is written as FULL OUTER JOIN or FULL JOIN, all variations perform one action, namely, return all columns from both tables, while the matching columns will be overlapped by the columns from the left table.
Example request:
View code SQL
| SELECT< select_list>FROM Table_A A FULL OUTER JOIN Table_B B ON A. Key = B. Key |
Left Excluding JOIN 
This query will return all columns from the left table (Table A) that do not match the columns from the right table (Table B).
Example request:
View code SQL
