JOIN

In the Ultra Transcenders books

DP-900DP-750DP-600PL-300DP-800

Each book explains JOIN in context, with comparison tables and the common traps.

Terms in this definition

• SELECT

  A DML command in SQL used to retrieve rows from one table or several.

• Pairing

  Deployment pipelines link an item in one stage to its counterpart in the next. Deploying overwrites a linked item, but an unlinked item that just shares its name gets copied across as a duplicate.

• Primary key

  Uniquely identifies every row in a relational table, using one column or several together. A foreign key in another table holds this value to reference the row.

• FK

  Short for foreign key: a column pointing at the primary key of a different table. Azure Databricks treats both key constraint types as information only and does not enforce them.

Related terms

• Anti join

  A join ([LEFT] ANTI JOIN in SQL, anti or left_anti in PySpark) returning just the left-hand rows that find no partner on the right, with only the left table's columns. A typical use is spotting fact rows whose dimension key is missing.

• Application security group

  Named collection of VM NICs that NSG rules can use as source or destination in place of IP addresses. NICs join only when explicitly added, and they must all belong to one VNet.

• APPLY

  Evaluates a table-valued expression for every row on its left, inside FROM. Think of OUTER APPLY as a left outer join and CROSS APPLY as an inner join.

• AQE

  Adaptive query execution: Spark re-plans a query while it runs, for instance turning a sort-merge join into a broadcast join, merging shuffle partitions, dealing with skew and propagating empty relations. Databricks advises leaving it enabled.

• Assume referential integrity

  A relationship setting in DirectQuery and Direct Lake models that lets the engine send faster INNER JOIN queries to the source rather than OUTER JOINs. Only switch it on when every key on the many side has a match, otherwise unmatched fact rows quietly vanish from results.

• Autopilot deployment profile

  Controls how the out-of-box experience runs on Windows Autopilot devices: which deployment mode and join type to use, and whether every targeted device should be converted to Autopilot. Up to 350 of these Intune profiles can exist in a tenant.

• Broadcast join

  A join method that copies the smaller table to every executor so that the larger table needs no shuffle. You can force it with the BROADCAST hint (or BROADCASTJOIN / MAPJOIN), or AQE may pick it while the query runs.

• Bulk enrolment

  A provisioning package from Windows Configuration Designer is applied to Windows devices so they join Microsoft Entra ID and enrol in Intune in one go. Its token is valid for 180 days, and MFA can't be used on the account inside the package.