8 ms·

I takes days/weeks to pick up the core of SQL: - create tables, update the schema, insert rows, add an index - select, filters, joins, order by, limit, inner

I takes days/weeks to pick up the core of SQL: - create tables, update the schema, insert rows, add an index - select, filters, joins, order by, limit, inner queries It takes forever to be comfortable with: - anything that involves summarizing, grouping, having, min, max, windows

deleted 5y ago
[deleted]
542458 5y ago
Personally I feel that it took me a while to get really comfortable with more complex joins. There’s an problem they used in the study that required joining a table with itself, and honestly I would probably take a while to come up with that answer, if at all.
- weaksauce 5y ago
  A basic approach(probably what they are going for in a basics study) would be something like this off the top of my head select c.cid, c2.cid from customer as c inner join customer as c2 on c.street = c2.street where c.city <> c2.city though that has reflective duplicates say (1, 5) would also have (5, 1) in the output. So I'm not sure if that's "allowed"
  - fifilura 5y ago
    There are lots of cases where a join with yourself is applicable, although they are mostly superseded by window functions these days. For example normalisation (join with a groupby/sum of yourself) or rank (join each row with all rows that have lower value than yourself and count those rows). But as I mentioned above. A good start is to sketch that out in excel. You will realize that what you need is another column (e.g. total sum for this id). And from that you can work yourself backwards to figure out what is the table you need to join with to create that column.
    - weaksauce 5y ago
      I'm not sure if you are replying to the wrong person but the question has nothing to do with a total sum of ids... the question was: "List all pairs of customer IDs who live on a street with the same name but in a different city." listed under self-join that said i haven't wrangled with raw sql in a spell so the reading on window functions is interesting.
      - fifilura 5y ago
        1. GP mentioned hairy self joins 2. You replied with an example 3. I pointed out that there are other examples. That said, I have a feeling your duplicates can be fixed by adding the requirement that c.cid < c2.cid Not sure a window function would help in this particular situation, but they are there to help in more mundane examples.
        weaksauce 5y ago
        I see how it can be read that way yeah. they mentioned the study used a self join problem that they couldn't likely figure out so I looked at the paper and quickly did it off the top of my head. the less than predicate does indeed do the trick.
myspy 5y ago
Something other which is hard is writing performant queries. Using statements with subqueries/in syntax for example. And I always forget which join does what.
- AdrianB1 5y ago
  Remembering which join does what is easy: inner joins strictly joins the tables, left takes all on left (first table), right takes all on right (second table), outer (or cross) join are so rarely used you don't need to memorize.
  - Semaphor 5y ago
    > outer (or cross) join are so rarely used you don't need to memorize. I’d say they are more important to know than right joins.
- MeinBlutIstBlau 5y ago
  same with the joins. on paper it makes sense, in practice it does not. if its more than a "select * from dbo.whatever where column abc = 'thing'" i have to refer back to notes and play with it.
  - eurasiantiger 5y ago
    Basically inner/left/right is a choice on how you want your NULLs: do you want all the rows that match, all the rows on the left, or all the rows on the right?
- deleted 5y ago
  [deleted]
pge 5y ago
and worst of all, anything that involves vendor-specific keywords…
fifilura 5y ago
I think one tip here is to always work with CTEs "WITH", and not nest queries. That way you can always go back and check "what is it I join with what", by querying the individual steps with LIMIT 10. The other tip is to sketch the problem in excel/google sheets when it gets hairy. Not the actual code (I don't have a clue how to do that, others have), just the values in the different steps. In the end it is only about rows and columns. But that said, these days a lot of it happens intuitively for me, I pretty much know the solution before I can spell it out. It certainly was not like that when I started. When you begin, "programming without for loops" feels like programming with your right hand tied behind your back. But in hindsight you get a lot of exercise in the immutable paradigms of functional programming, working with comprehensions, sets, maps folds comes very natural.
- odipar 5y ago
  Yep, CTEs are a huge boon to structure your SQL - use them where you can.
- moonchrome 5y ago
  Have some links on using CTEs to replace nested queries and how it helps ? I've used SQL enough to have to write nested queries, haven't dove further than that.
  - ducharmdev 5y ago
    Here's an article with some examples: https://learnsql.com/blog/sql-subquery-cte-difference/ https://learnsql.com/blog/sql-subquery-cte-difference/ You can think of CTEs as a way to save an intermediate query as a sort of temp variable/table that can be used in the final statement following `WITH something AS (...)`. They are great for flattening your queries and giving descriptive names to subqueries, and you can chain multiple WITHs together as well.
- prpl 5y ago
  This is good advice but sometimes you will need to nest things depending on database and optimizer and how it handles CTEs, especially if you ever mess with recursive things. An additional problem here is that there’s no standard way to represent EXPLAIN queries across systems so that makes an additional barrier to entry unfortunately.
AdrianB1 5y ago
Not really. I used to teach SQL not a long time ago and about 1/4 of the trainees were getting up to speed fast, about half in a reasonable time, the rest were there only because they were sent there by their managers. I found that the most important success factors in learning SQL is the analytical thinking of the trainee and the way the trainer is explaining the concepts, in what order and what examples are used (the best examples are the ones the trainees meet in their regular work). The functions are simple, the only difficulty is to remember the ones that are not used often enough (ex: some window functions). Even in that case, a quick check in the documentation is enough to get up to speed. The major difficulty with SQL is to write efficient queries on large data volumes, covered by the right indexes. This is very specific to each RDBMS, especially because of the tools helping with the work are specific (ex: SSMS, SQL Sentry Plan Explorer, statistics parser etc).
- MeinBlutIstBlau 5y ago
  If all you do is SQL, anybody can learn it quick. If you do full stack, you're only gonna care about what gives you the data you need at that time.
  - AdrianB1 5y ago
    No offence, but a full stack's job is not to write good SQL, it's to write enough SQL to get what is needed, then the development DBA's job is to make it fast and efficient. You don't need to be a great car mechanic to drive to the office and back.
    - jaza 5y ago
      At most places I've worked, there are no DBAs, and the devs are responsible for optimising all the SQL. On the occasion that there have been DBAs at the company I worked for, they always refused to help with any SQL, on the grounds that all SQL is "application level", and insisting that they were only responsible for configuring / deploying / monitoring the DB infrastructure.
      - 5y ago
btilly 5y ago
Meh, summarizing, grouping, etc aren't that hard. However WINDOW queries definitely have a learning curve. Not the least because useful examples almost always require you to use a nested query.
- magicalhippo 5y ago
  I learned SQL on a need to know basis. For me, recursive queries were the ones that needed the most time to click. Another one that caught me by surprise was NULL vs unknown[1]. That bit me in a couple of queries. [1]: https://learnsql.com/blog/understanding-use-null-sql/ https://learnsql.com/blog/understanding-use-null-sql/
  - layer8 5y ago
    The problem with NULL in SQL is that the semantics are inconsistent in complex ways. See for example https://vettabase.com/blog/what-does-null-mean-in-sql/ https://vettabase.com/blog/what-does-null-mean-in-sql/.
  - nicoburns 5y ago
    Recursive queries are definitely top of the complexity pile IMO. However I usually discount them because I am yet to actually need them in a production environment. Window functions are super useful on the other hand.
    - magicalhippo 5y ago
      Heh, perhaps it's more accurate to say I've abused recursive queries. One instance was to turn a column containing comma separated values into rows[1], so I could join on them. Wasn't for a query that needed performance of course. [1]: https://news.ycombinator.com/item?id=28020321 https://news.ycombinator.com/item?id=28020321
      - alexanderskates 5y ago
        I had this same issue in Redshift and ended up populating a table with values 1 to the maximum number of commas found (e.g. using max(regexp_count(...)) or something), then cross joining on the table with the csv column and calling split_part on the corresponding column and index (with the index coming from the numbers table). The cross join ensures that you index every value of the csv column.
branko_d 5y ago
Not so sure about aggregation, but what definitely takes forever is performance. To make a non-trivial SQL query scale to non-trivial amounts of data, you have to understand the physical data organization and how query optimizer is likely to use it, which is kind of contradictory to the idea of the SQL as a "declarative" language where you just say what you want, and let the query optimizer figure out how to get it. Instead, you have to design your indexes carefully to coax the optimizer into choosing a reasonable access path for your particular query. And do the same for all queries where performance is important. Indexes are fundamentally not about data, but about access patterns. Which is what the developers are responsible for. That's why physical database design is a development task, not database administration task.
- fomine3 5y ago
  I think RDB is one of the most leaky abstraction. Despite that, it's still very useful.
PostThisTooFast 5y ago
"You're posting too fast. Please slow down. Thanks." How about you fuck yourself. Thanks.

deleted 5y ago

[deleted]

542458 5y ago

Personally I feel that it took me a while to get really comfortable with more complex joins. There’s an problem they used in the study that required joining a table with itself, and honestly I would probably take a while to come up with that answer, if at all.

weaksauce 5y ago
A basic approach(probably what they are going for in a basics study) would be something like this off the top of my head select c.cid, c2.cid from customer as c inner join customer as c2 on c.street = c2.street where c.city <> c2.city though that has reflective duplicates say (1, 5) would also have (5, 1) in the output. So I'm not sure if that's "allowed"
- fifilura 5y ago
  There are lots of cases where a join with yourself is applicable, although they are mostly superseded by window functions these days. For example normalisation (join with a groupby/sum of yourself) or rank (join each row with all rows that have lower value than yourself and count those rows). But as I mentioned above. A good start is to sketch that out in excel. You will realize that what you need is another column (e.g. total sum for this id). And from that you can work yourself backwards to figure out what is the table you need to join with to create that column.
  - weaksauce 5y ago
    I'm not sure if you are replying to the wrong person but the question has nothing to do with a total sum of ids... the question was: "List all pairs of customer IDs who live on a street with the same name but in a different city." listed under self-join that said i haven't wrangled with raw sql in a spell so the reading on window functions is interesting.
    - fifilura 5y ago
      1. GP mentioned hairy self joins 2. You replied with an example 3. I pointed out that there are other examples. That said, I have a feeling your duplicates can be fixed by adding the requirement that c.cid < c2.cid Not sure a window function would help in this particular situation, but they are there to help in more mundane examples.
      - weaksauce 5y ago
        I see how it can be read that way yeah. they mentioned the study used a self join problem that they couldn't likely figure out so I looked at the paper and quickly did it off the top of my head. the less than predicate does indeed do the trick.

myspy 5y ago

Something other which is hard is writing performant queries. Using statements with subqueries/in syntax for example. And I always forget which join does what.

AdrianB1 5y ago
Remembering which join does what is easy: inner joins strictly joins the tables, left takes all on left (first table), right takes all on right (second table), outer (or cross) join are so rarely used you don't need to memorize.
- Semaphor 5y ago
  > outer (or cross) join are so rarely used you don't need to memorize. I’d say they are more important to know than right joins.
MeinBlutIstBlau 5y ago
same with the joins. on paper it makes sense, in practice it does not. if its more than a "select * from dbo.whatever where column abc = 'thing'" i have to refer back to notes and play with it.
- eurasiantiger 5y ago
  Basically inner/left/right is a choice on how you want your NULLs: do you want all the rows that match, all the rows on the left, or all the rows on the right?
deleted 5y ago
[deleted]

pge 5y ago

and worst of all, anything that involves vendor-specific keywords…

fifilura 5y ago

I think one tip here is to always work with CTEs "WITH", and not nest queries. That way you can always go back and check "what is it I join with what", by querying the individual steps with LIMIT 10. The other tip is to sketch the problem in excel/google sheets when it gets hairy. Not the actual code (I don't have a clue how to do that, others have), just the values in the different steps. In the end it is only about rows and columns. But that said, these days a lot of it happens intuitively for me, I pretty much know the solution before I can spell it out. It certainly was not like that when I started. When you begin, "programming without for loops" feels like programming with your right hand tied behind your back. But in hindsight you get a lot of exercise in the immutable paradigms of functional programming, working with comprehensions, sets, maps folds comes very natural.

odipar 5y ago
Yep, CTEs are a huge boon to structure your SQL - use them where you can.
moonchrome 5y ago
Have some links on using CTEs to replace nested queries and how it helps ? I've used SQL enough to have to write nested queries, haven't dove further than that.
- ducharmdev 5y ago
  Here's an article with some examples: https://learnsql.com/blog/sql-subquery-cte-difference/ https://learnsql.com/blog/sql-subquery-cte-difference/ You can think of CTEs as a way to save an intermediate query as a sort of temp variable/table that can be used in the final statement following `WITH something AS (...)`. They are great for flattening your queries and giving descriptive names to subqueries, and you can chain multiple WITHs together as well.
prpl 5y ago
This is good advice but sometimes you will need to nest things depending on database and optimizer and how it handles CTEs, especially if you ever mess with recursive things. An additional problem here is that there’s no standard way to represent EXPLAIN queries across systems so that makes an additional barrier to entry unfortunately.

AdrianB1 5y ago

Not really. I used to teach SQL not a long time ago and about 1/4 of the trainees were getting up to speed fast, about half in a reasonable time, the rest were there only because they were sent there by their managers. I found that the most important success factors in learning SQL is the analytical thinking of the trainee and the way the trainer is explaining the concepts, in what order and what examples are used (the best examples are the ones the trainees meet in their regular work). The functions are simple, the only difficulty is to remember the ones that are not used often enough (ex: some window functions). Even in that case, a quick check in the documentation is enough to get up to speed. The major difficulty with SQL is to write efficient queries on large data volumes, covered by the right indexes. This is very specific to each RDBMS, especially because of the tools helping with the work are specific (ex: SSMS, SQL Sentry Plan Explorer, statistics parser etc).

MeinBlutIstBlau 5y ago
If all you do is SQL, anybody can learn it quick. If you do full stack, you're only gonna care about what gives you the data you need at that time.
- AdrianB1 5y ago
  No offence, but a full stack's job is not to write good SQL, it's to write enough SQL to get what is needed, then the development DBA's job is to make it fast and efficient. You don't need to be a great car mechanic to drive to the office and back.
  - jaza 5y ago
    At most places I've worked, there are no DBAs, and the devs are responsible for optimising all the SQL. On the occasion that there have been DBAs at the company I worked for, they always refused to help with any SQL, on the grounds that all SQL is "application level", and insisting that they were only responsible for configuring / deploying / monitoring the DB infrastructure.
    - 5y ago

btilly 5y ago

Meh, summarizing, grouping, etc aren't that hard. However WINDOW queries definitely have a learning curve. Not the least because useful examples almost always require you to use a nested query.

magicalhippo 5y ago
I learned SQL on a need to know basis. For me, recursive queries were the ones that needed the most time to click. Another one that caught me by surprise was NULL vs unknown[1]. That bit me in a couple of queries. [1]: https://learnsql.com/blog/understanding-use-null-sql/ https://learnsql.com/blog/understanding-use-null-sql/
- layer8 5y ago
  The problem with NULL in SQL is that the semantics are inconsistent in complex ways. See for example https://vettabase.com/blog/what-does-null-mean-in-sql/ https://vettabase.com/blog/what-does-null-mean-in-sql/.
- nicoburns 5y ago
  Recursive queries are definitely top of the complexity pile IMO. However I usually discount them because I am yet to actually need them in a production environment. Window functions are super useful on the other hand.
  - magicalhippo 5y ago
    Heh, perhaps it's more accurate to say I've abused recursive queries. One instance was to turn a column containing comma separated values into rows[1], so I could join on them. Wasn't for a query that needed performance of course. [1]: https://news.ycombinator.com/item?id=28020321 https://news.ycombinator.com/item?id=28020321
    - alexanderskates 5y ago
      I had this same issue in Redshift and ended up populating a table with values 1 to the maximum number of commas found (e.g. using max(regexp_count(...)) or something), then cross joining on the table with the csv column and calling split_part on the corresponding column and index (with the index coming from the numbers table). The cross join ensures that you index every value of the csv column.

branko_d 5y ago

Not so sure about aggregation, but what definitely takes forever is performance. To make a non-trivial SQL query scale to non-trivial amounts of data, you have to understand the physical data organization and how query optimizer is likely to use it, which is kind of contradictory to the idea of the SQL as a "declarative" language where you just say what you want, and let the query optimizer figure out how to get it. Instead, you have to design your indexes carefully to coax the optimizer into choosing a reasonable access path for your particular query. And do the same for all queries where performance is important. Indexes are fundamentally not about data, but about access patterns. Which is what the developers are responsible for. That's why physical database design is a development task, not database administration task.

fomine3 5y ago
I think RDB is one of the most leaky abstraction. Despite that, it's still very useful.

PostThisTooFast 5y ago

"You're posting too fast. Please slow down. Thanks." How about you fuck yourself. Thanks.