Joins Galore

Kevin Feasel

2017-01-13

Syntax

Lukas Eder has a comprehensive guide to joining data using SQL:

Alternative syntaxes: NATURAL JOIN

An more extreme and much less useful form of "EQUI" JOIN is the NATURAL JOIN clause. The previous example could be further “improved” by replacing USING by NATURAL JOIN like this:

SELECT *
FROM actor
NATURAL JOIN film_actor
NATURAL JOIN film

Notice how we no longer need to specify any JOIN criteria, because a NATURAL JOIN will automatically take all the columns that share the same name from both tables that it joins and place them in a “hidden” USING clause. As we’ve seen before, as primary keys and foreign keys have the same column name, this appears quite useful.

There is a high likelihood that you will learn at least one new thing here; for example, check out lateral joins (which SQL Server practitioners know as something else).

Related Posts

What’s In SQL Server 2019 CTP 2.0?

Aaron Bertrand gives us the highlights: Certificate Management in Config Manager View and validate all of your certificates from a single interface, and manage and deploy certificate changes across all of the replicas in an Availability Group or all of the nodes in a Failover Cluster Instance. Built-in data classification A new ADD SENSITIVITY CLASSIFICATION statement helps you identify and […]

Read More

Multiple Mentions Of A Column In An UPDATE Statement

Doug Lane walks us through various scenarios with updates including the same column multiple times: An application developer came to me with this question recently: “Can I use the same column twice in a SQL UPDATE statement?” Yes and no. It depends on what you mean by “use”. Read on to see what Doug means.

Read More

Categories

January 2017
MTWTFSS
« Dec Feb »
 1
2345678
9101112131415
16171819202122
23242526272829
3031