Dear 365 Team,
I’m going through the Module 2 SQL course and am struggling a bit to grasp the purpose of the cross join and union commands. In particular, the last exercise in the “SQL Joins” chapter confused me where we had a SELECT-UNION clause in parantheses within a SELECT-clause; you even asked about the minus sign in the “ORDER BY -a.emp_no DESC”, yet did not deliver an explanation of either the minus sign nor the convoluted syntax itself. Googling it, I found most applications of the union/cross join clauses using such convoluted sets of code, but I am not sure how and why they function.
Would be great if you provided some explanations either here or directly into the exercise solution within the module!
Thanks and stay healthy,
Jan
Hi Jan!
Thanks for reaching out.
Regarding the minus sign, here’s an explanation you might find useful.
1) ORDER BY a.emp_no DESC;
Ending the relevant query this way, you will obtain an output order with the highest employee number on top, the smallest employee number down the list, and the null values at the end.
2) ORDER BY a.emp_no ASC;
This ending will do the opposite – the null values will be on top, and then the employee numbers will grow from the smallest to the highest.
3) ORDER BY -a.emp_no DESC;
Using this code (and this is the one provided in the article, or Lecture 192), first orders the employees from smallest to highest number, and leaves the null values at the end.
4) ORDER BY -a.emp_no ASC;
Following the logic explained so far, this ending would list the null value first, and will then order all employees from the highest to the lowest number.
Depending on the situation, you may choose between 1), 2), 3), and 4). In our example, we think 3) suits best; that’s why we ended the query with ORDER BY -a.emp_no DESC;
Then, UNION is about combining, or adding data vertically. So, if you have two sets of data that have the same columns containing values of the same data types, you can SELECT the first data set, use UNION, and then SELECT the second data set to combine them (thus letting the second data set appear after the first one).
CROSS JOIN has a totally different functionality. It will lead to an output containing all possible combinations of the fields you have designated, so it is sort of a horizontal matching of the columns from the two (or more) tables.
Hope this helps.
Best,
Martin