What Does the '=*' Syntax Mean in SQL Joins?

Understanding the Meaning of "=*" in SQL Joins

When examining Microsoft SQL Server code, it is possible to encounter an unfamiliar join convention: "=*." This syntax, which has historical roots before SQL Server 2005, has a specific meaning and is not an ANSI JOIN.

Syntax of the "=*" Join

The "=*" join syntax takes the following form:

WHERE table1.yr =* table2.yr -1

Definition

The "=*" join is an outer join that uses the following logic:

• If the left table (table1 in the example) contains a row with a matching value in the right table (table2), the rows are joined.
• If the left table (table1) has no matching value in the right table (table2), a null value is added for the columns of the right table.
• If the right table (table2) has no matching value in the left table (table1), a null value is added for the columns of the left table.

Example

Consider the following tables:

table1:
| yr | data |
| ----------- | -------- |
| 2022 | x |
| 2023 | y |

table2:
| yr | value |
| ----------- | --------- |
| 2021 | a |
| 2022 | b |

The following query uses the "=*" join to combine these tables:

SELECT *
FROM table1
WHERE table1.yr =* table2.yr -1;

This query would produce the following result:

yr	data	value
2022	x	b
2023	y	NULL

**Note:** The "-1" in the query subtracts one year from the "yr" column of table2, resulting in a match for "2022" from table1.

**Historical Significance and ANSI Joins**

The "=*" join syntax was prevalent in older versions of TSQL. However, since SQL Server 2005, ANSI JOIN syntax is preferred. The ANSI JOIN syntax uses keywords such as "INNER JOIN", "LEFT JOIN", and "RIGHT JOIN" to specify the type of join.

The above is the detailed content of What Does the '=*' Syntax Mean in SQL Joins?. For more information, please follow other related articles on the PHP Chinese website!