SQL Joins

Cross join-both table name after from. This is the worst way to join tables because there are duplicate rows. The reason for this is because each row will be combined. If you have 4 rows in table1 and 2 rows in table2 this join creates 8 rows instead of 4 with the new info from table 1.

SELECT * FROM table1, table2

implicit inner join- do a cross join but use WHERE to say what columns are related

SELECT * FROM table1, table2 WHERE table.columnA=table2.columnB

explicit inner join- using JOIN -select one table then JOIN for 2nd table and instead of WHERE like an implicit inner join use ON. After the tables are joined the WHERE operator can then be used.

SELECT * FROM table1 JOIN table2 ON table1.columnA = table2.columnB

Outer Join -LEFT OUTER JOIN, keeps every row in left table even if there is no info from table2, if this happens then there is a null

RIGHT OUTER JOIN -Opposite of left outer join.

Self joins -Column in table related to column in same table. An alias is needed to differentiate the table.

SELECT * FROM table1 JOIN table1 alias ON table1.column1 = alias.column3

Combining multiple joins — At times you are going to need to combine multiple self joins. When this happens you’ll need to use aliases for the tables so SQL can differentiate between the different joins