Building And Organizing Complex Queries Mission: Understanding the description of screen 5

I am currently doing the Building And Organizing Complex Queries mission.

I have trouble understanding the description of screen 5 regarding Creating Rows with Union

Specifically I do not understand the highlighted part of the screenshot I provide.
Any help would be appreciated!

1 Like

Hi @Pristakos

In case you still need some help on this question.

A JOIN clause helps us connect/ join two tables and fetch results based on matching conditions between two or more tables (from both or one of these tables, depending on the type of JOIN). We have to match values in common columns between the two tables.

For example, an Employee table can tell us Xyz employee works in dept_id 01. But which dept is represented by 01 is present in department table. In order to obtain a result that can tell us Xyz employee is in the “Admin” department, we will have to connect these two tables. The common column to connect/join would be “dept_code”. The query would look something like this:

SELECT emp.emp_id, emp.emp_name, emp.emp_salary, dept.dept_name as department
FROM emp 
JOIN dept
WHERE emp.dept_id = dept.dept_id;

Here the joins are table and column-based. You need to have a common element between these two or more tables. The column names can be different but the data inside them has to be the same!

On the other hand, the UNION operator helps us combine two tables (or more) based on common rows.
Let’s say the 1st query has the following output:

E01 | Xyz  | 2000 | Admin |
E02 | Abc | 2000 | Admin |
E03 | Def | 4500 | IT |
E04 | Stu | 4500 | Sales |  

And a 2nd query has the following output:

M01 | Dga | 5000 | Admin |
E02  | Abc  | 2000 | Admin |
E03 | Def | 4500 | IT |
M02 | Bnv | 4000 | Accounting |

If we combine these two queries

1st query 
UNION
2nd query 

we will get the following result:

E01 | Xyz  | 2000 | Admin |
E02 | Abc | 2000 | Admin |
E03 | Def | 4500 | IT |
E04 | Stu | 4500 | Sales |  
M01 | Dga | 5000 | Admin |
M02 | Bnv | 4000 | Accounting |

It will filter out the duplicate records (provided they are duplicates entirely) and fetch you distinct records from both the queries.

Here you are not combining these two queries/ tables using a column. You are using rows to stack them one over the other. Hence the course content said, “not tables and not columns”. UNION will have to satisfy the 2 conditions:

  • No. of columns need to be the same
  • The datatype of columns needs to be the same, hence columns should be in sequence.

In case you want to see the duplicate records as well, try with the UNION ALL operator. Hope this helps.

1 Like

Thanks @Rucha for your time and the detailed explanation!
:slight_smile:

1 Like

Thank you @Pristakos!