The only exercise I have complete on joins was the first in ‘Joining Data in SQL’, which was this:
SELECT * FROM facts
INNER JOIN cities ON facts.id = cities.facts_id
Not only is that the first join exercise, but the first time we saw columns accessible with the TableName[dot]Column syntax.
This exercise ends with this:
SELECT c.*, f.name country_name FROM facts f
INNER JOIN cities c ON c.facts_id = f.id
This is an unreasonable jump in complexity and I’m struggling to understand it. I believe I found the answers poking around in the SQL, but please assist.
We have never seen or used TableName[dot]Column in the SELECT statement. It also is not in a single example from the exercise. Without explanation, I originally suspected the first line could be:
SELECT c.*, name FROM facts
and have the same output, because we are specifying where name is from. I am assuming TableName[dot]Column syntax is required when selecting specific columns with joins. Is this correct?
When selecting specific columns, do we always have to start with the columns from the table we will be specifying in the inner join?
Using the alias from the inner join INNER JOIN cities c in the select statement SELECT c.* requires some foresight. So, the logical way to approach joins would be to think of the INNER JOIN table first and what alias you plan to assign it to, if question 2 is correct. It may be helpful to have a step by step logical approach in writing here.
It seemed to be an option to use the AS statement or not, but the DQ answers omit it. As a beginner, it is much easier to track the SQL with the AS statement included. Is it okay to still include it, or is it best practice to omit it in the industry?
I would add the above 1-4 to the exercise (answers and corrections included), and maybe practice selecting the columns with no alias like below first.
SELECT cities.*, facts.name country_name FROM facts
INNER JOIN cities ON cities.facts_id = facts.id
I hope this does not come off as me being upset, I just want to be direct with the questions and feedback. Thank you kindly.
Not at all! Thank you very much for this feedback. I agree with you that this is a big jump.
It’s good form to specify the table. Whether it is necessary or not, possibly depends on the SQL engine we’re using. I’d venture guessing that most would only require that you specify the table if the column name you’re using is present in more than one of the intervening tables.
No, in fact, you don’t even need to include the columns used in the ON condition.
It’s best to include it as it makes the code more readable, but if you happen to work at a place where they don’t use it, then it’s probably best not to use AS, to comply with your team’s standards.