CYBER WEEK - EXTRA SAVINGS EVENT
TRY A FREE LESSON

Combining Rows Using Intersect and Except

Section: https://app.dataquest.io/m/190/building-and-organizing-complex-queries/6/combining-rows-using-intersect-and-except

WITH customers_usa_gt_90 AS
   (
    SELECT * FROM customer_usa
    INTERSECT
    SELECT * FROM customer_gt_90_dollars
   )
   
SELECT 
     e.first_name || " " || e.last_name employee_name,
     COUNT(c.customer_id) customers_usa_gt_90      
FROM employee e
LEFT JOIN customers_usa_gt_90 c ON c.support_rep_id = e.employee_id
WHERE e.title = 'Sales Support Agent'
GROUP BY 1 ORDER BY 1;

Can somebody help me out here. I feel like I’m being overloaded with information here in regards to CREATE VIEW, WITH, and combining ROWS.

Question 1) Why are we doing WITH customers_usa_gt_90 AS instead of WITH customer_usa here?

Question 2) When do we use INTERSECT (or UNION/ EXCEPT) ie: how does it apply to this case? I’m assuming in this case that we’re taking anything from customer_usa & customer_gt_90dollars and seeing which one intersects? So in this case any customers that are in USA and have spent over $90? While I can see that after the fact, I had to get help to come to that conclusion. Is there tricks or tips that will help me to come to this conclusion if I’m come across it in a real life situation?

Question3) LEFT JOIN customers_usa_gt_90 c ON c.support_rep_id = e.employee_id

Why are we using LEFT JOIN here instead of INNER. I think similar to the above question, I’m having issues identifying which JOIN to use still.

Question 4) Where do we pull the WITH clause here? In the previous samples we would use FROM customers_usa_gt_90 at the bottom of our query but in this case we used FROM employee e.

1 Like

@Dataquest.io, here I am just sharing my knowledge what I learnt, please do provide feedback and suggestion. I am on learning phase only.

Question 1) Why are we doing WITH customers_usa_gt_90 AS instead of WITH customer_usa here?

Answer 1) When we use WITH we are actually creating a customized virtual table as per our requirement through SQL query. Now when we create any table we need to provide a name to it, so through alias AS we are giving our created customized virtual table a name. Besides we use WITH just to make our SQL query clean, readable and understandable.

Question 2) When do we use INTERSECT (or UNION / EXCEPT ) ie: how does it apply to this case? I’m assuming in this case that we’re taking anything from customer_usa & customer_gt_90dollars and seeing which one intersects? So in this case any customers that are in USA and have spent over $90? While I can see that after the fact, I had to get help to come to that conclusion. Is there tricks or tips that will help me to come to this conclusion if I’m come across it in a real life situation?

Answer 2) INTERSECT you can consider the working of it same as INNER JOIN, but INTERSECT helps you to join two virtual tables on row-wise.

Question3) LEFT JOIN customers_usa_gt_90 c ON c.support_rep_id = e.employee_id

Why are we using LEFT JOIN here instead of INNER . I think similar to the above question, I’m having issues identifying which JOIN to use still.

Answer 3) LEFT JOIN is used when we want all the information from left table irrespective of right table matches the criteria of selection or not. So here we want all the employees information irrespective of whether they have served/supported/gave customer-service to any customer or not.

Question 4) Where do we pull the WITH clause here? In the previous samples we would use FROM customers_usa_gt_90 at the bottom of our query but in this case we used FROM employee e .

Answer 4) WITH is used to create a customized virtual table as per our requirement, and through it we can extract information with our SQL queries. Besides we use WITH to make our SQL query clean, readable and understandable rather then making a very big complicated and messy query.
FROM is used to refer tables - Dataset or virtual tables.

And Dataquest.io I too need help to understand the difference between CREATE VIEW and WITH.

1 Like

This is mostly good. Here’s some feedback.

Personally, I would prefer to use blockquotes for the questions instead of the answers. Not only do I think it looks better, it’s the actual purpose of a blockquote.

Regarding the first answer, you’re not saying anything that isn’t true, but it’s not clear to me that you’re answering the question.

I don’t have a good answer for the first question either, though. My answer would be that we’re using customers_usa_gt_90 because it’s a sufficiently descriptive name, but would could also use the name this_is_a_name. Also, customer_usa was already used previously.

A view is an entity in the database that stays with it, like a table. You have tables in a database, you finish work, you go home and come back the following day to find the tables in the database. Views are the same. The same doesn’t happen with whatever is created with WITH.

This begs the question, what’s the difference between a table and a view? Depending on how you use a database, they might not look different to you at all, but they are formally different objects.

It’s bit a like asking what’s the difference between the objects used in the Python code below.

>>> print(3)
3
>>> print("3")
3

They look the same when printed, but we know that they are very different things.

Some of the differences between views and tables is that views aren’t things that you update or delete rows from. Views are saved queries, basically.

2 Likes

Hi @Bruno,

Thanks for guidance on how to use blockquotes. It was really informative, and frankly I didn’t knew it. :slight_smile: I have done the needful changes.

Yes for first question I had read hurriedly so I misunderstood the question thinking the question was for use of AS keyword. Yes you are right. Just want to add here is when we create virtual tables using WITH it is needed to give different names to different virtual table in the same query statement. Same like you cannot have two files of same name in same drive so here it is virtual tables in virtual environment.

Thanks for explanation of CREATE VIEW and WITH. Just one question does CREATE VIEW create a dummy table on our hard-drive?

This possibly depends on the database. In my experience, the only thing that is saved is the actual query, not the results (i.e. the table). For instance, this is definitely true in Postgres:

CREATE VIEW defines a view of a query. The view is not physically materialized. Instead, the query is run every time the view is referenced in a query.

2 Likes

Hi Bruno,

Thanks for the explanation.

2 Likes