[sql] Difference between INNER JOIN and LEFT SEMI JOIN

An INNER JOIN can return data from the columns from both tables, and can duplicate values of records on either side have more than one match. A LEFT SEMI JOIN can only return columns from the left-hand table, and yields one of each record from the left-hand table where there is one or more matches in the right-hand table (regardless of the number of matches). It's equivalent to (in standard SQL):

SELECT name
FROM table_1 a
WHERE EXISTS(
    SELECT * FROM table_2 b WHERE (a.name=b.name))

If there are multiple matching rows in the right-hand column, an INNER JOIN will return one row for each match on the right table, while a LEFT SEMI JOIN only returns the rows from the left table, regardless of the number of matching rows on the right side. That's why you're seeing a different number of rows in your result.

I am trying to get the names within table_1 that only appear in table_2.

Then a LEFT SEMI JOIN is the appropriate query to use.

Examples related to sql

Passing multiple values for same variable in stored procedure SQL permissions for roles Generic XSLT Search and Replace template Access And/Or exclusions Pyspark: Filter dataframe based on multiple conditions Subtracting 1 day from a timestamp date PYODBC--Data source name not found and no default driver specified select rows in sql with latest date for each ID repeated multiple times ALTER TABLE DROP COLUMN failed because one or more objects access this column Create Local SQL Server database

Examples related to hql

Difference between INNER JOIN and LEFT SEMI JOIN [Ljava.lang.Object; cannot be cast to HQL Hibernate INNER JOIN What is the difference between JOIN and JOIN FETCH when using JPA and Hibernate IN-clause in HQL or Java Persistence Query Language ORDER BY using Criteria API How do you do a limit query in JPQL or HQL? Hibernate HQL Query : How to set a Collection as a named parameter of a Query? How do you create a Distinct query in HQL JPA and Hibernate - Criteria vs. JPQL or HQL

Examples related to hive

select rows in sql with latest date for each ID repeated multiple times PySpark: withColumn() with two conditions and three outcomes java.lang.RuntimeException: Unable to instantiate org.apache.hadoop.hive.ql.metadata.SessionHiveMetaStoreClient Hive cast string to date dd-MM-yyyy How to save DataFrame directly to Hive? How to calculate Date difference in Hive Select top 2 rows in Hive Just get column names from hive table Create hive table using "as select" or "like" and also specify delimiter Hive Alter table change Column Name