New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

explanation of UNION vs UNION ALL #256

Open
raybellwaves opened this Issue Aug 26, 2018 · 5 comments

Comments

Projects
None yet
3 participants
@raybellwaves
Contributor

raybellwaves commented Aug 26, 2018

Given that ALL hasn't been introduced so far and the example works the same without it do you think it is worth dropping it?

Changing

SELECT AVG(a) FROM (
    SELECT 1 AS a
    UNION ALL SELECT NULL
    UNION ALL SELECT 5);

to

SELECT AVG(a) FROM (
    SELECT 1 AS a
    UNION SELECT NULL
    UNION SELECT 5);
@henrykironde

This comment has been minimized.

Collaborator

henrykironde commented Aug 26, 2018

@raybellwaves for the issue. We shall discus this and find a way of better representing the solution.
Personally, I think there should be an explanation of UNION and UNION ALL in that solution.

@remram44

This comment has been minimized.

Collaborator

remram44 commented Aug 26, 2018

UNION ALL is the simpler construct IMHO. It does less than UNION and is therefore faster. I believe it is more widely used than UNION. People are likely to encounter it "in the wild" and therefore I don't think it's right to just remove it.

The fact that the keyword is two words instead of one doesn't mean anything (ALL is not a keyword and does nothing by itself).

But I agree that episode 04-calc should be updated to introduce both. We could also introduce the syntax you see there, using UNION ALL to build a table literal in the query.

@raybellwaves

This comment has been minimized.

Contributor

raybellwaves commented Aug 27, 2018

Thanks for the discussion on this. This is my first exposure to SQL to so I didn't know UNION ALL was the construct. A quick google pointed me to https://www.w3schools.com/sql/sql_any_all.asp and I thought similar to python numpy all would return some kind of boolean.

Discussion about UNION and UNION ALL beforehand would be useful.

@raybellwaves raybellwaves changed the title from drop use of ALL in 06-agg 'Averaging with NULL' exercise to explanation of UNION vs UNION ALL Aug 27, 2018

@henrykironde

This comment has been minimized.

Collaborator

henrykironde commented Aug 27, 2018

Thanks @remram44 and @raybellwaves.
@raybellwaves, do you have time and can put in a pull request for this issue?

@raybellwaves

This comment has been minimized.

Contributor

raybellwaves commented Aug 27, 2018

I have 0 SQL experience. I would rather leave this to someone who is more knowledgeable.

donalus pushed a commit to donalus/sql-novice-survey that referenced this issue Oct 11, 2018

Merge pull request swcarpentry#256 from maxim-belkin/static-get-val
lesson_check: get_val is a static method
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment