SQL JOIN Reduces Row Count Problem

I’ve been thinking about the issue where row (record) counts decrease when using SQL JOINs.

I’m not very familiar with SQL so I don’t know how to explain it properly, but when you join tables, if the original table doesn’t have the same columns as the joined table, the columns that don’t exist will be output in a deleted state. Therefore, the number of output rows decreases, which is inconvenient when you want to include all information from the select source table plus the xx column from the joined table for regex searching with where.

…I really can’t explain it properly.

In such cases, using left solves the problem.

SELECT u.id, u.url, u.visit_count, u.title FROM urls AS u

36254 rows returned

SELECT u.id, u.url, u.visit_count, u.title, kw.term FROM urls AS u
JOIN keyword_search_terms AS kw ON kw.url_id = u.id
JOIN visits AS v ON v.url = u.id

18520 rows returned

SELECT u.id, u.url, u.visit_count, u.title, kw.term FROM urls AS u
left JOIN keyword_search_terms AS kw ON kw.url_id = u.id
left JOIN visits AS v ON v.url = u.id

74926 rows returned

Just declare left before join.

sql join keep all records - Google Search sql join decreased rows - Google Search SQL Server : left join results in fewer rows than in left table - Stack Overflow

Since we’re here, let’s dig a bit deeper.

There appear to be four types of joins.

(INNER) JOIN: Returns records that have matching values in both tables LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table FULL (OUTER) JOIN: Return all records when there is a match in either left or right table

SQL Joins

mysql - Selecting all items in one table and join with another table, allowing nulls - Stack Overflow

The most versatile one seems to be LEFT.

When I tried to execute right in sqlite, it said it’s not supported.

RIGHT and FULL OUTER JOINs are not currently supported