What is semi-join in database?

by

Simple example. Let’s select students with grades using left outer join:

SELECT DISTINCT s.id
FROM  students s
      LEFT JOIN grades g ON g.student_id = s.id
WHERE g.student_id IS NOT NULL

Now the same with left semi-join:

SELECT s.id
FROM  students s
WHERE EXISTS (SELECT 1 FROM grades g
              WHERE g.student_id = s.id)

The latter is generally more efficient (depending on concrete DBMS and query optimizer).

More Related Contents:

  1. What is the difference between “INNER JOIN” and “OUTER JOIN”?
  2. What is the difference between Left, Right, Outer and Inner Joins? [duplicate]
  3. What is the difference between JOIN and UNION?
  4. SQLite – How do you join tables from different databases?
  5. Venn Diagram for Natural Join
  6. Which one of these Queries are wrong and which is correct (SQL) [closed]
  7. SQL Selecting from three tables
  8. Explicit vs implicit SQL joins
  9. Entity Attribute Value Database vs. strict Relational Model Ecommerce
  10. How can I schedule a job to run a SQL query daily?
  11. Reset identity seed after deleting records in SQL Server
  12. Generate a resultset of incrementing dates in TSQL
  13. Left Outer Join Not Working?
  14. Database Design for Revisions?
  15. Simulate CREATE DATABASE IF NOT EXISTS for PostgreSQL?
  16. Does SQLite3 not support foreign key constraints?
  17. Inner Join vs Natural Join vs USING clause: are there any advantages?
  18. Address standardization within a database
  19. Correct use of transactions in SQL Server
  20. SQL recursive query on self referencing table (Oracle)
  21. Generate script in SQL Server Management Studio
  22. NOT NULL constraint over a set of columns
  23. What are projection and selection?
  24. Database efficiency – table per user vs. table of users
  25. Why do multiple-table joins produce duplicate rows?
  26. SQL ON DELETE CASCADE, Which Way Does the Deletion Occur?
  27. Find rows that have the same value on a column in MySQL
  28. IN vs. JOIN with large rowsets
  29. Why are foreign keys more used in theory than in practice?
  30. What is difference between foreign key and reference key?

Leave a Comment