Sql Joins - Differences

Transact-SQL
1

New to SQL. Having a great deal of difficulty in differentiating the various joins. Doing sq exercises online and getting well over 90 % of the code wrong. In particular - Inner Join, full outer join, left outer join, right outer join, self join, and cross join. Venn diagrams are understood but not helping. What I need are definitive words in describing an sql exercise that would point to what join to be used. I have written down words describing what each join is supposed to do, but that is still not helping. I'm looking for key words that point me in the right direction. I have not found on the internet what those words would be.

2

hi

hope this helps

1 Like
3

Post one of the "exercises" that you've gotten incorrect about Inner Joins and we'll start from there.

Also, do you know what a join column is?

1 Like
4

I just finished another set of 27 sql join exercises, and got 22 of them wrong for picking the wrong joins.

Venn diagrams do not help.

It is how the questions are worded - that is the key.

Example - 6. From the following tables, write a SQL query to find all departments, including those without employees. Return first name, last name, department ID, department name.

I chose a left outer join, and the correct answer was right outer join.

How? Why? What determines from that wording that it will be a right outer join?

Here is another one:

10. From the following tables, write a SQL query to find out which employees have or do not have a department. Return first name, last name, department ID, department name.

I chose a join - correct answer was left outer join.

Again, what were the key words that determined that it was a left outer join?

Attached is a word document showing the 2 files that are used in both exercises.

Join columns appear to be keys.

Thanks.

5

Can you post the full question including the "following tables" and the list of possible answers for the question?

Same for question 10, please.

6

I cannot see any attachment but:

This means show all departments with or without employees. It can be written as either a LEFT OUTER JOIN or a RIGHT OUTER JOIN depending on the order of the tables in the FROM clause:
This:

SELECT E.first_name, E.last_name, D.department_id, D.department_name
FROM departments D
	LEFT OUTER JOIN employees E
		ON D.department_id = E.employee_id;

is the same as:

SELECT E.first_name, E.last_name, D.department_id, D.department_name
FROM employees E
	RIGHT OUTER JOIN departments D
		ON E.department_id = D.employee_id;

This means show all employees with or without departments. Again it can be written as either a LEFT OUTER JOIN or a RIGHT OUTER JOIN depending on the order of the tables in the FROM clause:
This:

SELECT E.first_name, E.last_name, D.department_id, D.department_name
FROM employees E
	LEFT OUTER JOIN departments D
		ON E.department_id = D.employee_id;

is the same as:

SELECT E.first_name, E.last_name, D.department_id, D.department_name
FROM departments D
	RIGHT OUTER JOIN employees E
		ON D.department_id = E.employee_id;

ie For a LEFT JOIN all rows in the left hand table are shown and for a RIGHT JOIN all rows in the RIGHT hand table are shown. Guess what a FULL OUTER JOIN does?!

For code readability a lot of shops say that, where possible, order the tables so a LEFT JOIN is used.

7

Thanks. SQL joins are becoming more clear.