SQL SEMI and ANTI Joins

preview_player
Показать описание
The SEMI and ANTI joins are great for filtering data based on the existence of data in another table.

Confusingly, SEMI and ANTI joins aren't really "joins". There's a traditional way of implementing SEMI/ANTI joins that all databases will support, and a few databases (like DuckDB!) actually provide SEMI/ANTI join types to make the SQL a little more friendly.

---------------------------------------------------

This is part of the Everything About Joins playlist, available at:

The written version of this content is also available at:

---------------------------------------------------

CHAPTERS

00:00 Intro
01:12 Sample data
01:48 Problem statement
02:14 Filtering with WHERE ... IN
03:50 Filtering with WHERE EXISTS
07:19 DuckDB Syntax
10:35 Wrap up
  1. Bill Wallis
  2. sql
  3. joins
Рекомендации по теме
SQL SEMI and 0:12:24

SQL SEMI and ANTI Joins

Types of Joins 0:14:03

Types of Joins in SQL | Semi-joins, Equi-Joins, outer join, inner join, left join, anti join

6 SQL Joins 0:09:47

6 SQL Joins you MUST know! (Animated + Practice)

Master SQL Joins 0:09:42

Master SQL Joins Tutorial | Inner Join, Outer Join, Anti Join, Equi Join, Theta Join, Semi Join

SQL Basics: How 0:03:20

SQL Basics: How To Use A LEFT ANTI JOIN and Why

SQL Server Quickie 0:04:57

SQL Server Quickie #39 - Semi Joins

SQL Basics: How 0:04:33

SQL Basics: How To Use A LEFT SEMI JOIN and Why

Performance Tuning Semi 0:19:57

Performance Tuning Semi and Anti-Semi Joins In SQL Server

SQL Joins Interview 0:14:03

SQL Joins Interview Question and Answers #2 | Cross Anti Semi Join | What Does SQL Joins Return

Semi Joins in 0:12:18

Semi Joins in Sql Server

SQL Joins in 0:04:28

SQL Joins in 4 Minutes | Full Visual Explanation | Inner Join, Outer Join, Semi Join, Cross Join

SQL : what 0:00:58

SQL : what is the difference between an anti-join and an anti semi join?

MySQL SELF JOINS 0:10:03

MySQL SELF JOINS are ... ughhh

Joins in SQL. 0:10:23

Joins in SQL. Einfach erklärt (Left-Join, Right-Join, Cross-Join, Left-Anti-Join)

30. join() function 0:14:11

30. join() function in PySpark Continuation | Left semi, Left anti & self join | Azure Databrick...

SQL : What 0:00:59

SQL : What is semi-join in database?

SQL Joins - 0:35:58

SQL Joins - Inner, Left, Right, Full Outer, Semi, Anti, Cartesian | Cardinalities: One-to-One, etc

SQL Join Types 0:30:49

SQL Join Types - Inner, Outer, Semi-join, Anti-join (English) with Amr Elhelw - Tech Vault

Heard of SQL 0:00:21

Heard of SQL Joins? What About an Anti-Join?

13.18b Relationale Algebra: 0:05:21

13.18b Relationale Algebra: Semi Joins, Anti Semi Joins

SQL Review of 1:27:47

SQL Review of JOIN Types

SQL ANTI JOIN 0:00:56

SQL ANTI JOIN Vs. SEMI JOIN ✍️

Semi outer join 0:04:31

Semi outer join in sql | semi join in relational algebra Part 23/32

Left Semi Join, 0:12:06

Left Semi Join, Right Semi Join, Anti Joins in Sql Server

Комментарии
Автор

Both DuckDB and ClickHouse support explicit SEMI/ANTI joins:


While DuckDB implements SEMI/ANTI as a new join type (alongside LEFT, RIGHT, FULL, etc), ClickHouse implements SEMI/ANTI as a new join scope (alongside INNER, OUTER, etc)

Bilbottom
Автор

this was insanely good! thank you so much. now i finally understand what these joins are. duckdb rocks!

michalbotor