Last Updated: 2022-12-01
This means no constraints - i.e. a CROSS JOIN - a result set in which every row in each table is joined to every row in the other table; this is also called a cartesian product.
e.g. in Laravel I can join my
locations table (which contains data specific to
my web-app) to my
migrations table (which exclusively contains db meta-data).
This join makes no sense logically since they have connection. But it can still
select * from locations join migrations; id test_station_id house_number street city postcode country latitude longitude created_at updated_at id migration batch 2 2 52 Martinistrasse Hamburg 20251 Germany 53.59024800 9.97848000 2020-05-05 17:05:08 2020-05-05 17:05:08 3 2020_05_04_123257_create_test_stations 1 1 1 1 Charitépl Berlin 10117 Germany 52.51635000 13.37755000 2020-05-05 17:05:08 2020-05-05 17:05:08 4 2020_05_05_115937_create_slots 1 2 2 52 Martinistrasse Hamburg 20251 Germany 53.59024800 9.97848000 2020-05-05 17:05:08 2020-05-05 17:05:08 4 2020_05_05_115937_create_slots 1 1 1 1 Charitépl Berlin 10117 Germany 52.51635000 13.37755000 2020-05-05 17:05:08 2020-05-05 17:05:08 5 2020_05_05_123541_create_tickets 1 2 2 52 Martinistrasse Hamburg 20251 Germany 53.59024800 9.97848000 2020-05-05 17:05:08 2020-05-05 17:05:08 5 2020_05_05_123541_create_tickets 1 ...
There is no difference between these two!
Another way to think about this: an inner join cannot be left/right since it
exclusively returns entries that match on both sides. Therefore a
left join or
right join have to be outer joins.
Say you have the following schema - a User has many Products, which might be modelled in Rails with:
class User < ActiveRecord::Base has_many :products end class Product < ActiveRecord::Base belongs_to :user end
These are the numbers of records in each table.
Product.count => 2040 User.count => 400
Assuming that each product has to be have a user, what is the count of their inner join?
Most people think it's upper limit has to be 400, being limited by the number of records in the smaller table. That thinking is wrong. It will probably be some number between 400 and 2040. Why? Because inner join basically creates a virtual table with one row per match. If somes users have multiple products, then there will be multiple rows, bringing the number closer to the number of rows in the products table.