Understanding SQL JOINS

A JOIN clause is used to combine rows from two or more tables, based on a related column between them. - w3schools

Be aware that same column name doesn't always mean they are the ones to create a JOIN on. In most cases, students table would have id as column name for studentId column. Clearly, column name has nothing to do with creating relationship. You must know that yeah that studentId from fees table is id/studentId column in students table.

SELECT fees.FeeId, students.name, fees.paidDate 
FROM fees INNER JOIN students 
ON students.studentId=fees.studentId 
WHERE feeId='145237';

Inner Join

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

We can also write JOIN instead of INNER JOIN. JOIN is same as INNER JOIN.

Left Join

Now table 1 and table 2 are confusing terms, How I mentally remember this is, LEFT JOIN will select all the records of the table that we specify after the FROM clause and matching records from the other table.

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name = table2.column_name;

Right Join

If you find table 1/2 a confusing terminology then I'd suggest use the way, how I try to remember this. I would say select only matching records from the table specified after FROM clause and all the records from the joined table.

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name = table2.column_name;

Full Join

SELECT column_name(s)
FROM table1
FULL JOIN table2
ON table1.column_name = table2.column_name;

Please note that Mysql doesn't support FULL JOIN but there are ways we can simulate it. Now I can write a whole article on it but I'd rather suggest to read this blog post.

Cross Join

SELECT *
FROM fees
CROSS JOIN students

CROSS JOIN can potentially return very large result-sets! If you add a WHERE clause with CROSS JOIN, it would return same result as an INNER JOIN query.

Conclusion