News for hackers. Stuff that matters.
Re:Show HN: Select Star SQL, an interactive SQL book by dm8 on Monday September 03, 2018 @09:58PM (#17906085)
This is good intiative. We need to simplify pedagogy.

On a related note, I always found the syntax of SQL to be great. Someone told me that simplicity of SQL made relational databases even more popular. It is one of the best designed language where even someone new to the field won't feel intimidated. I'm surprised designers of SQL haven't won the Turing Award yet.

Re:Show HN: Select Star SQL, an interactive SQL book by m_st on Tuesday September 04, 2018 @03:59AM (#17907376)
I agree except one thing: SELECT cols FROM table. This should really be FROM table SELECT cols as every modern SQL editor will then be able to show you a list of available columns.

See LINQ. There they had the opportunity to fix it and it works just great.

Re:Show HN: Select Star SQL, an interactive SQL book by piaste on Tuesday September 04, 2018 @12:30PM (#17910262)
More generally, statements should appear in the same order as they are executed, as far as this is possible.

This nullifies some other common sources of confusion, such as which statements are executed before a GROUP BY and which ones after (and why HAVING exists as a keyword). It's also similar to how CTE syntax is generally much more readable than subqueries.

Re:Show HN: Select Star SQL, an interactive SQL book by hesk on Wednesday September 05, 2018 @05:03PM (#17921900)
The order of clauses in a SQL statement should have no relation on the order of execution because SQL is a declarative query language. The query specifies what the user wants and not how the system should compute the answer.

This declarative property also provides a (somewhat weak) motivation of why SELECT should come first. Precisely because it answers part of the WHAT, i.e., the schema of the result.

Re:Show HN: Select Star SQL, an interactive SQL book by techno_modus on Tuesday September 04, 2018 @07:55AM (#17908350)
I personally think first about the loop/iterator organization (FROM in SQL) and only after that what I am going to do with its elements.

However,

    SELECT a1,a2,... FROM Table AS t WHERE Condition
is syntactically equivalent to Python list comprehension:

    [(t.a1,t.a2,...) for t in Table if Condition]
Here the use of attributes (SELECT) is also written before the iterator.

SELECT is also analogous to normal loops:

    foreach t in Table
      if not Condition: continue
      # Use t.a1, t.a2 etc.
Here we first provide the loop specification while the usage of elements is written only in the body.
Re:Show HN: Select Star SQL, an interactive SQL book by lozzo on Tuesday September 04, 2018 @09:19AM (#17908817)
I concur. I always start with select * from table because I cannot possibly remember all the right column names
Re:Show HN: Select Star SQL, an interactive SQL book by nexxer on Wednesday September 12, 2018 @09:38AM (#17968565)
In the clients I've used (SQLYog, Mysql Workbench), aliasing a table lets you use autocomplete when referencing that alias followed by a dot.

So, I will start with a SELECT * FROM myTable t, then go back and replace * with t.<columns appear here>.

I'll use the same in other places in the query like WHERE conditions.

Re:Show HN: Select Star SQL, an interactive SQL book by jrockway on Tuesday September 04, 2018 @04:10AM (#17907421)
I mean, it works either way. Once you type the column names, it can guess the table name easily.

I use DataGrip at work and it can complete the column names without knowing the table name, so it ends up not being a problem.

Re:Show HN: Select Star SQL, an interactive SQL book by dragonwriter on Tuesday September 04, 2018 @12:48PM (#17910469)
> Once you type the column names, it can guess the table name easily.

If the query is simple enough that a natural join would work and you aren't doing FROM clause aliasing (which can be useful to make reusable queries self-documenting), sure.

For more complex queries and obviously any time table or column aliasing are used, that becomes somewhere between less likely and logically impossible.

Re:Show HN: Select Star SQL, an interactive SQL book by icc97 on Tuesday September 04, 2018 @03:49PM (#17912222)
I'd rather have the enjoyment of being able to think in normal English speak rather than Yoda speak just to fix one edge case that appeared decades after they came up with the syntax.
Re:Show HN: Select Star SQL, an interactive SQL book by sqldba on Tuesday September 04, 2018 @07:06AM (#17908118)
It’s also how azure query language works.
Re:Show HN: Select Star SQL, an interactive SQL book by wvenable on Monday September 03, 2018 @10:06PM (#17906117)
I teach SQL to financial people and, for the most part, it isn't too bad. When I get to joins, I teach a subset of SQL where all joins are done through INNER JOIN / LEFT JOIN syntax and that helps a lot. Joins are the part that everyone has trouble with.
Re:Show HN: Select Star SQL, an interactive SQL book by setr on Monday September 03, 2018 @10:56PM (#17906278)
Tbh I usually see teachers worried about joins but its the group by/having clauses that I always watched people struggle with (at least, writing it).

Ofc that might just be the emphasis on joins makes group by the next lowest hanging fruit

Re:Show HN: Select Star SQL, an interactive SQL book by ranit on Tuesday September 04, 2018 @12:23AM (#17906627)
What do your abbreviations mean?
Re:Show HN: Select Star SQL, an interactive SQL book by grzm on Tuesday September 04, 2018 @12:25AM (#17906636)
tbh = to be honest, ofc = of course

Urban Dictionary (https://www.urbandictionary.com) or even just typing something like "what does tbh mean?" into your preferred search engine will often help you find likely interpretations of unknown abbreviations, in my experience.

Re:Show HN: Select Star SQL, an interactive SQL book by justinator on Tuesday September 04, 2018 @01:01AM (#17906763)
> or even just typing something like "what does tbh mean?" into your preferred search engine will often help you find likely interpretations

I mean, that's what they did.

Re:Show HN: Select Star SQL, an interactive SQL book by asavinov on Tuesday September 04, 2018 @03:15AM (#17907183)
> Joins are the part that everyone has trouble with.

There is significant problem-solution mismatch in joins and some other SQL constructs which are therefore semantically quite controversial in many use cases:

https://github.com/asavinov/bistro#why-column-orientation

Re:Show HN: Select Star SQL, an interactive SQL book by HeavyStorm on Tuesday September 04, 2018 @10:50AM (#17909422)
The syntax isn't the problem though. Join relational operation is though on itself.
Re:Show HN: Select Star SQL, an interactive SQL book by Exuma on Tuesday September 04, 2018 @01:23AM (#17906825)
Can you explain when to really use a lateral join?
Re:Show HN: Select Star SQL, an interactive SQL book by garyclarke27 on Tuesday September 04, 2018 @03:25AM (#17907227)
In Postgresql, Lateral Joins enable function chaining for set returning functions. ie you can feed the results of earlier srf function to the arguments of following function. This gets around one of Postgresql most glaring omissions - table valued parameters. Is incredibly useful once you get used to them.
Re:Show HN: Select Star SQL, an interactive SQL book by andreareina on Tuesday September 04, 2018 @10:22AM (#17909224)
I might be misunderstanding your point, but Postgresql functions can take (and return) tables:

    CREATE FUNCTION fullname(person) RETURNS TEXT AS $$
    SELECT $1.firstname || ' ' || $1.lastname
    $$ LANGUAGE SQL
Could you go into a more detail wrt using lateral joins with set returning functions? I'm used to using them as a way to "extend" the row with later terms depending on prior terms as you say, but I'm not comprehending the srf case so well (or tbh the results that pop up when I search for "posrgresql set returning function").
Re:Show HN: Select Star SQL, an interactive SQL book by netcraft on Tuesday September 04, 2018 @09:23AM (#17908831)
In my experience it’s relatively rare that you need them. I think of them as the for-each of joins. It evaluates row by row, not a single time. Another analogy would be a correlated subquery.
Re:Show HN: Select Star SQL, an interactive SQL book by lixtra on Tuesday September 04, 2018 @12:20AM (#17906618)
I write a lot of sql and it is generally a nice language.

But for my taste the language is to close to English and therefore redundant and inconsistent in an annoying way. Example:

Select x from tbl ...

Delete from tbl ...

Update tbl set x=...

Why change the word order?

Re:Show HN: Select Star SQL, an interactive SQL book by garyclarke27 on Tuesday September 04, 2018 @03:32AM (#17907262)
Yes,I also suggest standards committee allow the option of putting FROM clause first - this would make it easier for beginners to grok what’s going on and enable better tooling such as auto-complete.
Re:Show HN: Select Star SQL, an interactive SQL book by pjmlp on Tuesday September 04, 2018 @03:07AM (#17907157)
On PC the alternatives were xBase, FoxPro, Paradox and Access, so it was a no brainer that SQL eventually took over.
Re:Show HN: Select Star SQL, an interactive SQL book by hyperpallium on Tuesday September 04, 2018 @01:43AM (#17906890)
Since there weren't any commercial relational databases available pre-SQL, this is quite true.

Codd wrote a couple of query languages before, but they apparently weren't usable by mere mortals.

SQL is over 40 years old, and still dominates. The most recent contenders had to position themselves in terms of SQL (NoSQL).

Re:Show HN: Select Star SQL, an interactive SQL book by codeisawesome on Tuesday September 04, 2018 @02:25AM (#17907009)
Also interesting how even NoSQL (non relational) Data stores are embracing SQL or some subset or flavouring of it.