Understanding SQL joins once and for all

Understanding SQL joins is fundamental to any java programmer who needs to fetch data from database. SQL joins can be very confusing. Here is a very simple Venn diagram that help you understand all the important SQL joins pretty quickly.. 

SQL Joins Venn Diagram

INNER JOIN: 

Select only those rows that have values in common in the columns specified in the ON clause. Records available in both the tables. 

LEFT, RIGHT, or FULL OUTER JOIN:  

Select all rows from the table on the left (or right, or both) regardless of whether the other table has values in common and (usually) enter NULL where data is missing.  (Note:  FULL OUTER JOIN not implemented in Access.)

CROSS JOIN:

Select all possible combinations of  rows and columns from both tables (Cartesian product). If tableA has n records and tableB has m records then a cross join will produce n x m results.. joining every records from tableA with every single record from tableB. So if tableA has 30 records and tableB has 35 records the cross join will produce 30x35 = 1050 records in the result set. (This is Not A Good join - the query may run for a very long time  and produce a huge, not very useful result set.) 

           

MINUS:

You get this by specifying a where clause saying not to include records having a match in tableB.  

Note:

There is nothing like left inner join or right inner join. They both mean inner join, they all are one and the same.