Finding all records without associated ones
It may perform better doing a left join:
SELECT
teams.*
FROM teams
LEFT JOIN players ON (teams.id = players.team_id)
WHERE
players.team_id IS NULL
Or using ARel (thanks to JasonKing's comment):
Team.includes(:players).where('players.team_id IS NULL')
Want to find records with no associated records in Rails
This is still pretty close to SQL, but it should get everyone with no friends in the first case:
Person.where('id NOT IN (SELECT DISTINCT(person_id) FROM friends)')
Find all records which have a count of an association of zero and none-zero
The reason is that in SQL a count of zero happens when there are no rows. So if there are no rows, even group, there is no result.
What you want is
Image.left_joins(:associated_images).where(associated_images: {id: nil}).group('images.id')
When SQL does a left join, for an image which does not have an associated image, it fills in NULL for all the columns in the associated_images
table. So the ones where the associated_images.id
is nil, are the ones we want.
Find records that do not have related records in SQL
We use JOIN to find related data. To find data without any related data, we can use an anti-join.
The following joins the tables, then selects those without any order items. This tends to be more efficient that a WHERE id NOT IN (...)
style query.
select *
from
Orders O
left outer join OrderItems I
on I.OrderId = O.Id
where
I.Id is null
Rails - find records without specific type of associated records
I think you want a left join for this. Unfortunately Rails doesn't make this easy. But you can still use SQL fragments:
joins("LEFT JOIN assignments ON assignments.project_id = projects.id AND assignments.kind = 'worker'").where(assignments: {id: nil})
So SQL does a LEFT JOIN on assignments where the assignment kind is 'worker', but only selects rows for which there was no matching assignment.
Finding all records that has at least one association from associated models (tables)
I'd use such query:
Tree.left_joins(:fruits, :flowers).where('fruits.id IS NOT NULL OR flowers.id IS NOT NULL').distinct
it will produce this SQL:
SELECT DISTINCT "trees".* FROM "trees" LEFT OUTER JOIN "fruits" ON "fruits"."tree_id" = "trees"."id" LEFT OUTER JOIN "flowers" ON "flowers"."tree_id" = "trees"."id" WHERE (fruits.id IS NOT NULL OR flowers.id IS NOT NULL)
How to select all records from one table that do not exist in another table?
SELECT t1.name
FROM table1 t1
LEFT JOIN table2 t2 ON t2.name = t1.name
WHERE t2.name IS NULL
Q: What is happening here?
A: Conceptually, we select all rows from table1
and for each row we attempt to find a row in table2
with the same value for the name
column. If there is no such row, we just leave the table2
portion of our result empty for that row. Then we constrain our selection by picking only those rows in the result where the matching row does not exist. Finally, We ignore all fields from our result except for the name
column (the one we are sure that exists, from table1
).
While it may not be the most performant method possible in all cases, it should work in basically every database engine ever that attempts to implement ANSI 92 SQL
Related Topics
Bulk Insert with Variable File Name
SQL Server After Update Trigger
How to Get a Value from Previous Result Row of a Select Statement
SQL Server, Converting Seconds to Minutes, Hours, Days
Comma Separated Values in One Column - SQL Server
Issues with SQL Comparison and Null Values
Is There Any Other Way to Create Constraints During SQL Table Creation
Rails Active Query Order by Multiple Values in Specific Order
It's Possible to Create a Rule in Preceding Rows in Sum
SQL Server Dynamic Pivot for an Unknow Number of Columns
Insert Xml File in SQL via Ssis
Generate a Unique Column Sequence Value Based on a Query Handling Concurrency
Recursive Subquerying with Sorting
How to Change a Pg Column to Nullable True