Currently learning Data Science, here is what I've learnt so far. Pt.4

Today I learned how to work with multiple tables, it was a bit confusing to understand some behaviors but I kept going and practicing and I think it went really well, here are my notes. As always, feel free to comment any tips, corrections, or suggestions you might have for me. Thx! ;).

Combining tables:

JOIN

SELECT *
FROM table_name
JOIN second_table_name
  ON first_table.column_name = second_table.column_name;

SELECT table_name1.column_name,
   table_name2.column_name2
FROM table_name1
JOIN table_name2
  ON table1.column_name = table2.column_name;

LEFT JOIN
If the columns have different data, the unmatched data when joining columns using JOIN will be excluded, LEFT JOIN is a way to prevent this. With LEFT JOIN, if a join condition is not met, it will be filled with a NULL value.

SELECT *
FROM table_name1
LEFT JOIN table_name2
  ON table_name1.column_name = table_name2.column_name;

Primary Key: a column from each table which is unique to the table, it identifies every column on the table:

• None of the values can be NULL
• Each value must be unique
• A table can't have more than one Primary Key column

Foreign key: a primary key which appears in a different table.

They can be used to JOIN tables

CROSS JOIN

It is used to combine all rows of one table with all rows of another table. Does not require an ON statement. It is commonly used to compare values.

SELECT table1.column1,
   table2.column 2
FROM table1
CROSS JOIN table2;

UNION

It is used to stack one dataset on top of the other. It has a few rules:

• Tables must have the same number of columns.
• The columns must have the same data types in the same order as the first table.

SELECT *
FROM table1
UNION
SELECT *
FROM table2;

WITH

Combine two tables, but one of them is the result of a calculation. It assigns a name to a query, fits it in a parenthesis, and allows us to use it as if it were a table.

WITH query_name AS ( *write query here
   SELECT ...
   ...
   ...
   ...
)
SELECT *
FROM query_name
JOIN table_name
  ON _____ = _____;