Table JOIN for ANSI SQL
This topic provides reference content comparing table join functionality between Microsoft SQL Server 2019 and Amazon Aurora MySQL. You can understand the similarities and differences in join syntax and support between these two database systems.
Feature compatibility | Amazon SCT / Amazon DMS automation level | Amazon SCT action code index | Key differences |
---|---|---|---|
|
|
Basic syntax compatible. |
SQL Server Usage
SQL Server supports the standard ANSI join types:
-
<Set A> CROSS JOIN <Set B>
— Results in a Cartesian product of the two sets. EveryJOIN
starts as a Cartesian product. -
<Set A> INNER JOIN <Set B> ON <Join Condition>
— Filters the cartesian product to only the rows where the join predicate evaluates toTRUE
. -
<Set A> LEFT OUTER JOIN <Set B> ON <Join Condition>
— Adds to theINNER JOIN
all the rows from the reserved left set with NULL for all the columns that come from the right set. -
<Set A> RIGHT OUTER JOIN <Set B> ON <Join Condition>
— Adds to theINNER JOIN
all the rows from the reserved right set with NULL for all the columns that come from the left set. -
<Set A> FULL OUTER JOIN <Set B> ON <Join Condition>
— Designates both sets as reserved and adds non matching rows from both, similar to aLEFT OUTER JOIN
and aRIGHT OUTER JOIN
.
APPLY
SQL Server also supports the APPLY
operator, which is somewhat similar to a join. However, APPLY
operators enable the creation of a correlation between <Set A>
and <Set B>
such as that <Set B>
may consist of a subquery, a VALUES
row value constructor, or a table valued function that is evaluated for each row of <Set A>
where the <Set B>
query can reference columns from the current row in <Set A>
. This functionality isn’t possible with any type of standard JOIN
operator.
There are two APPLY
types:
-
<Set A> CROSS APPLY <Set B>
— Similar to aCROSS JOIN
in the sense that every row from<Set A>
is matched with every row from<Set B>
. -
<Set A> OUTER APPLY <Set B>
— Similar to aLEFT OUTER JOIN
in the sense that rows from<Set A>
are returned even if the sub query for<Set B>
produces an empty set. In that case, NULL is assigned to all columns of<Set B>
.
ANSI SQL 89 JOIN Syntax
Up until SQL Server version 2008 R2, SQL Server also supported the old style JOIN
syntax including LEFT
and` RIGHT OUTER JOIN`.
The ANSI syntax for a CROSS JOIN
operator was to list the sets in the FROM
clause using commas as separators. Consider the following example:
SELECT * FROM Table1, Table2, Table3...
To perform an INNER JOIN
, you only needed to add the JOIN
predicate as part of the WHERE
clause. Consider the following example:
SELECT * FROM Table1, Table2 WHERE Table1.Column1 = Table2.Column1
Although the ANSI standard didn’t specify outer joins at the time, most RDBMS supported them in one way or another. T-SQL supported outer joins by adding an asterisk to the left or the right of equality sign of the join predicate to designate the reserved table. Consider the following example:
SELECT * FROM Table1, Table2 WHERE Table1.Column1 *= Table2.Column1
To perform a FULL OUTER JOIN
, asterisks were placed on both sides of the equality sign of the join predicate.
As of SQL Server 2008R2, outer joins using this syntax have been deprecated. For more information, see Deprecated Database Engine Features in SQL Server 2008 R2
Note
Even though inner joins using the ANSI SQL 89 syntax are still supported, they are highly discouraged due to being notorious for introducing hard to catch programming bugs.
Syntax
CROSS JOIN
FROM <Table Source 1> CROSS JOIN <Table Source 2>
-- ANSI 89 FROM <Table Source 1>, <Table Source 2>
INNER / OUTER JOIN
FROM <Table Source 1> [ { INNER | { { LEFT | RIGHT | FULL } [ OUTER ] } }] JOIN <Table Source 2> ON <JOIN Predicate>
-- ANSI 89 FROM <Table Source 1>, <Table Source 2> WHERE <Join Predicate> <Join Predicate>:: <Table Source 1 Expression> | = | *= | =* | *=* <Table Source 2 Expression>
APPLY
FROM <Table Source 1>
{ CROSS | OUTER } APPLY
<Table Source 2>
<Table Source 2>:: <SELECT sub-query> | <Table Valued UDF> | <VALUES clause>
Examples
Create the Orders
and Items
tables.
CREATE TABLE Items ( Item VARCHAR(20) NOT NULL PRIMARY KEY Category VARCHAR(20) NOT NULL, Material VARCHAR(20) NOT NULL );
INSERT INTO Items (Item, Category, Material) VALUES ('M8 Bolt', 'Metric Bolts', 'Stainless Steel'), ('M8 Nut', 'Metric Nuts', 'Stainless Steel'), ('M8 Washer', 'Metric Washers', 'Stainless Steel'), ('3/8" Bolt', 'Imperial Bolts', 'Brass')
CREATE TABLE OrderItems ( OrderID INT NOT NULL, Item VARCHAR(20) NOT NULL REFERENCES Items(Item), Quantity SMALLINT NOT NULL, PRIMARY KEY(OrderID, Item) );
INSERT INTO OrderItems (OrderID, Item, Quantity) VALUES (1, 'M8 Bolt', 100), (2, 'M8 Nut', 100), (3, 'M8 Washer', 200)
INNER JOIN
SELECT * FROM Items AS I INNER JOIN OrderItems AS OI ON I.Item = OI.Item; -- ANSI SQL 89 SELECT * FROM Items AS I, OrderItems AS OI WHERE I.Item = OI.Item;
LEFT OUTER JOIN
Find Items that were never ordered.
SELECT Item FROM Items AS I LEFT OUTER JOIN OrderItems AS OI ON I.Item = OI.Item WHERE OI.OrderID IS NULL; -- ANSI SQL 89 SELECT Item FROM ( SELECT I.Item, O.OrderID FROM Items AS I, OrderItems AS OI WHERE I.Item *= OI.Item ) AS LeftJoined WHERE LeftJoined.OrderID IS NULL;
FULL OUTER JOIN
CREATE TABLE T1(Col1 INT, COl2 CHAR(2)); CREATE TABLE T2(Col1 INT, COl2 CHAR(2)); INSERT INTO T1 (Col1, Col2) VALUES (1, 'A'), (2,'B'); INSERT INTO T2 (Col1, Col2) VALUES (2,'BB'), (3,'CC'); SELECT * FROM T1 FULL OUTER JOIN T2 ON T1.Col1 = T2.Col1;
Result: Col1 COl2 Col1 COl2 1 A NULL NULL 2 B 2 BB NULL NULL 3 CC
For more information, see FROM clause plus JOIN, APPLY, PIVOT (Transact-SQL)
MySQL Usage
Amazon Aurora MySQL-Compatible Edition (Aurora MySQL) supports the following types of joins in the same way as SQL Server, except for FULL OUTER JOIN
:
-
<Set A> CROSS JOIN <Set B>
— Results in a Cartesian product of the two sets. EveryJOIN
starts as a Cartesian product. -
<Set A> INNER JOIN <Set B> ON <Join Condition>
— Filters the Cartesian product to only the rows where the join predicate evaluates toTRUE
. -
<Set A> LEFT OUTER JOIN <Set B> ON <Join Condition>
— Adds to theINNER JOIN
all the rows from the reserved left set with NULL for all the columns that come from the right set. -
<Set A> RIGHT OUTER JOIN <Set B> ON <Join Condition>
— Adds to theINNER JOIN
all the rows from the reserved right set with NULL for all the columns that come from the left set.
In addition, Aurora MySQL supports the following join types not supported by SQL Server:
-
<Set A> NATURAL [INNER | LEFT OUTER | RIGHT OUTER ] JOIN <Set B>
— Implicitly assumes that the join predicate consists of all columns with the same name from<Set A>
and<Set B>
. -
<Set A> STRAIGHT_JOIN <Set B>
— Forces<Set A>
to be read before<Set B>
and is used as an optimizer hint.
Aurora MySQL also supports the USING
clause as an alternative to the ON
clause. The USING
clause consists of a list of comma separated columns that must appear in both tables. The join predicate is the equivalent of an AND
logical operator for equality predicates of each column. For example, the following two joins are equivalent:
FROM Table1 INNER JOIN Table2 ON Table1.Column1 = Table2.column1;
FROM Table1 INNER JOIN Table2 USING (Column1);
If Column1
is the only column with a common name between Table1
and Table2
, the following statement is also equivalent:
FROM Table1 NATURAL JOIN Table2
Note
Aurora MySQL supports the ANSI SQL 89 syntax for joins using commas in the FROM
clause, but only for inner joins.
Note
Aurora MySQL supports neither APPLY
nor the equivalent LATERAL JOIN
used by some other database engines.
Syntax
FROM <Table Source 1> CROSS JOIN <Table Source 2> | <Table Source 1> INNER JOIN <Table Source 2> ON <Join Predicate> | USING (Equality Comparison Column List) | <Table Source 1> {LEFT|RIGHT} [OUTER] JOIN <Table Source 2> ON <Join Predicate> | USING (Equality Comparison Column List) | <Table Source 1> NATURAL [INNER | {LEFT|RIGHT} [OUTER]] JOIN <Table Source 2> | <Table Source 1> STRAIGHT_JOIN <Table Source 2> | <Table Source 1> STRAIGHT_JOIN <Table Source 2> ON <Join Predicate>
Migration Considerations
For most joins, the syntax should be equivalent and no rewrites should be needed.
-
CROSS JOIN
using either ANSI SQL 89 or ANSI SQL 92 syntax. -
INNER JOIN
using either ANSI SQL 89 or ANSI SQL 92 syntax. -
OUTER JOIN
using the ANSI SQL 92 syntax only.
FULL OUTER JOIN
and OUTER JOIN
using the pre-ANSI SQL 92 syntax aren’t supported, but they can be easily worked around.
CROSS APPLY
and OUTER APPLY
aren’t supported and need to be rewritten.
Examples
Create the Orders
and Items
tables.
CREATE TABLE Items ( Item VARCHAR(20) NOT NULL PRIMARY KEY Category VARCHAR(20) NOT NULL, Material VARCHAR(20) NOT NULL );
INSERT INTO Items (Item, Category, Material) VALUES ('M8 Bolt', 'Metric Bolts', 'Stainless Steel'), ('M8 Nut', 'Metric Nuts', 'Stainless Steel'), ('M8 Washer', 'Metric Washers', 'Stainless Steel'), ('3/8" Bolt', 'Imperial Bolts', 'Brass')
CREATE TABLE OrderItems ( OrderID INT NOT NULL, Item VARCHAR(20) NOT NULL REFERENCES Items(Item), Quantity SMALLINT NOT NULL, PRIMARY KEY(OrderID, Item) );
INSERT INTO OrderItems (OrderID, Item, Quantity) VALUES (1, 'M8 Bolt', 100), (2, 'M8 Nut', 100), (3, 'M8 Washer', 200)
INNER JOIN and OUTER JOIN
SELECT * FROM Items AS I INNER JOIN OrderItems AS OI ON I.Item = OI.Item; -- ANSI SQL 89 SELECT * FROM Items AS I, Orders AS O WHERE I.Item = OI.Item;
LEFT OUTER JOIN
SELECT Item FROM Items AS I LEFT OUTER JOIN OrderItems AS OI ON I.Item = OI.Item WHERE OI.OrderID IS NULL;
Rewrite for FULL OUTER JOIN
CREATE TABLE T1(Col1 INT, COl2 CHAR(2)); CREATE TABLE T2(Col1 INT, COl2 CHAR(2)); INSERT INTO T1 (Col1, Col2) VALUES (1, 'A'), (2,'B'); INSERT INTO T2 (Col1, Col2) VALUES (2,'BB'), (3,'CC'); SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.Col1 = T2.Col1 UNION ALL SELECT NULL, NULL, Col1, Col2 FROM T2 WHERE Col1 NOT IN (SELECT Col1 FROM T1);
Result: Col1 COl2 Col1 COl2 1 A NULL NULL 2 B 2 BB NULL NULL 3 CC
Summary
Table of similarities, differences, and key migration considerations.
SQL Server | Aurora MySQL | Comments |
---|---|---|
|
Supported |
|
|
Supported |
|
|
Not supported |
Requires T-SQL rewrite post SQL Server 2008 R2. |
|
Supported |
|
|
Not Supported |
Rewrite required. |
Not Supported |
|
Not recommended, may cause unexpected issues if table structure changes. |
Not Supported |
|
|
Not Supported |
|
For more information, see JOIN Clause