0

SELECT table1.column1, table2.column2... FROM table1 INNER JOIN table2 ON table1.common_field = table2.common_field;

Here is the template I found online for INNER JOIN ON.

Here is what I have understand so far.

There are two tables with data. We need data from both.

SELECT is used to display FROM is from what table do we wish to SELECT(display) INNER JOIN creates a new result table by combining column values of table 1 and 2

This is as far as I've understood. I'm struggling with ON table1.common_field = table2.common_field;

Especially the word "ON" is really not making any sense to me.

If someone could kindly explain this to me I'd be most thankfull

Hamzah Khan
  • 23
  • 4
  • 1
    [This](https://blog.codinghorror.com/a-visual-explanation-of-sql-joins/) is a good explanation – JohnHC Feb 04 '20 at 09:05
  • INNER JOIN is a intersection between 2 tables. In order for the intersection to be done we need a common column between these tables. ON clause specifies which is the common column for each on those tables – apomene Feb 04 '20 at 09:05
  • INNER JOIN - matches results from both tables where (ON) common_field in table1 is equal to common_field in table2 – Kuba Do Feb 04 '20 at 09:07
  • 1
    Logically `FROM table1 INNER JOIN table2` gives a `CROSS JOIN` with every permutation of rows form both tables. The predicate in the `ON` clause then determines which rows to keep from that. If you use `1=1` as the join predicate this is always true so you just get the `CROSS JOIN`. `table1.common_field = table2.common_field` will just preserve the rows where that condition is met. Physically this is likely to be implemented much more efficiently than generating the cross join and preserving the rows to keep but the logical description is still true. – Martin Smith Feb 04 '20 at 09:08
  • 1
    @JohnHC: no it's not. Those dreaded Venn diagrams completely miss the point that a JOIN between two tables in a one-to-many relationship will duplicate information from the "one" entity - which is something that most newcomers find very surprising. –  Feb 04 '20 at 09:57
  • @a_horse_with_no_name given the OP's demonstrated level of knowledge, it's a good starting point to understand the base concepts. Of course there is more to it, but that can come later. – JohnHC Feb 04 '20 at 11:24
  • Thank you all who responded. – Hamzah Khan Feb 04 '20 at 18:09
  • Does it matter which side the common columns come after the ON predicate e.g. SELECT FirstName, LastName FROM Employee_Data INNER JOIN Department ON Department.EmployeeID = Employee_Data.EmployeeID. Would it matter if we said ...ON Employee_Data.EmployeeID = Department.EmployeeID – Hamzah Khan Feb 05 '20 at 05:43
  • If you understand my comment above you should know the answer is "no". There are no circumstances where `Department.EmployeeID = Employee_Data.EmployeeID` is true but `Employee_Data.EmployeeID = Department.EmployeeID` isn't. It is like saying that `1 + 3 == 4` but `4 != 1 + 3` – Martin Smith Feb 05 '20 at 07:14

0 Answers0