[SQL 배우기] Section 5. JOINS

The Complete SQL Bootcamp for the Manipulation and Analysis of Data | Udemy

 

AS: creates an alias for a column to be called into a different name

• SELECT column AS new_name FROM table
• we cannot use an alias inside the WHERE operator because it gets executed at the very end of a query
• useful for renaming the output of the aggregate function

JOIN: allows to combine multiple tables

• main reason for different JOIN types is to decide how to deal with information only present in one of the joined tables

INNER JOIN: result with the set of records that match in both tables

• SELECT * FROM tableA INNER JOIN tableB ON tableA.col_match = tableB.col_match
• symmetrical
• matching column is a duplicate column
• in PostgreSQL, JOIN = INNER JOIN

FULL OUTER JOIN: set of records that are in either or both of the tables

• SELECT * FROM tableA FULL OUTER JOIN tableB ON tableA.col_match = tableB.col_match

• 

• Symmetrical
• filling empty values with null
• using WHERE can be used to filter null columns

LEFT OUTER JOIN: set of records that are in the left table, and values that are not in the right table are considered null

• SELECT * FROM tableA LEFT JOIN tableB ON tableA.col_match = tableB.col_match

• 

• Order matters

 

RIGHT OUTER JOIN: set of records that are in the right table, and values that are not in the left table are considered null

• SELECT * FROM tableA RIGHT JOIN tableB ON tableA.col_match = tableB.col_match

• 

• Order matters

 

UNION: concatenating two tables together

• SELECT column_names FROM table1 UNION SELECT column_names FROM table2
• should match in length