What is SQL (7)

Aliases

When you select a column or a table, it might be a good idea to rename them to make them more readable. Here is the Syntax:

To rename a column: SELECT Column_name AS alias_name FROM table_name;

To rename a table: SELECT Column_name FROM table_name AS alias_name;

For example:

‘US City’:

SELECT City AS IL City
FROM US City
WHERE State = 'IL';

The result will look like this:

This simple example may not show the power of alias, since the name is clearly enough. Alias will be very helpful when we start to JOIN tables together that I will explain later.

JOIN

We used the US City table for all the previous examples. Right now, we just created another table with all the temperature information for each city. How to join the two tables together so that we can have the lists of cities and their daily temperatures? Here comes the JOIN. JOIN is used to combine rows from two or more tables. There are four different joins, and I will explain each of them. To illustrate, here are the two table examples to use. The code column of the two tables represent the same city.

‘US_City’:

‘US_City_Temperature’

INNER JOIN （JOIN)

This is the most common type of join, and it is very intuitive. An INNER JOIN returns all rows from multiple tables where the join condition is met. To join two tables together. There must be one column in common to combine two tables. Otherwise, system does not know which two rows should join together. Here is the syntax:

SELECT column_name...
FROM table1
INNER JOIN table2
ON table1.column_name = table2.column_name;

OR

SELECT column_name...
FROM table1
JOIN table2
ON table1.column_name = table2.column_name;

Since this is the most common JOIN. We do not need to write down ‘INNER’ specifically.

Here is the Venn Diagram to illustrate. I got this picture from W3school, which is a great website to learn SQL, HTML and so on.

W3School: InnerJoin Venn Diagram

In our example, we can combine the two tables by writing down:

SELECT City, State, Temperature, Date
FROM US_City
(INNER) JOIN US_City_Temperature 
ON US_City.Code = US_City_Temperature.Code

The result will look like this:

Since we combine the two tables based on the same code number, the system can combine the two tables correctly. Otherwise, the system may combine the temperature of Miami with Chicago, which will cause error.

Since this is INNER JOIN, the system will only choose city information that have the same code on both tables. If there are cities only show on one table, not both, this city’s information will be disregard when we combine the two tables.