It's because it's logically in the wrong order, it should be:

   FROM Users 
   WHERE Type = 'Foo'
   SELECT id, name

They use the right order in a lot of ORMs and as I was a SQL expert (but not master), I found it so jarring at first.

You probably have the reverse problem, it doesn't fit your mental model which is in fact the right logical model.

It gets even worse when you add LIMIT/TOP or GROUP BY. SQL is great in a lot of ways, but logically not very consistent. And UPDATE now I think about it, in SQL Server you get this bizarreness:

    UPDATE u
    SET u.Type = 'Bar'
    FROM Users u
    JOIN Company c on u.companyId = c.id
    WHERE c.name = 'Baz'

▲ skydhash 6 days ago | parent | next [-]

That's because in the relational model, you deal mostly in terms of projections. There's an action and then the rest of the statement is creating the projection of the data the action will apply to. The action always applies to the whole of the projection (IIRC).d

The semantics of SQL and a standard programming language are quite different as they are based on different computing/data model.

▲ cultofmetatron 6 days ago | parent | prev [-]

you would LOVE ecto. its an elixir dsl for writing sql and fixes all the issues I have with sql including what you just mentioned here

▲ mattmanser 6 days ago | parent [-]

I actually still love writing SQL to be honest,

I can't have explained myself well, I find the SQL way "normal" even though it's logically/semantically a bit silly.

Because that's how I learnt.

My point was, if you learnt on ORMs, the SQL way must be jarring.

▲ cultofmetatron 6 days ago | parent [-]

can't stand orms and I wrote one myself a long time ago.

BUT

ecto isnt' an orm. its a sql dsl and it take a lot of pain out of writing your sql while being very easy to map what you're writing to teh output dsl

so instead of

``` select Users.id, count(posts.id) as posts_count from Users left join Posts on Posts.user_id = Users.id group by users.id ```

you can write ``` from(u in User) |> join(:left, [u], p in Post, on: u.id = p.user_id, as: :posts) |> select([u, posts: p], %{ id: u.ud, posts_count: count(p.id) }) |> group_by([u], u.id)

```

the |> you see here is a pipe operator. I've effectively decomposed the large block query into a series of function calls.

you can assign subqueries as separate values and join into those as well. it doesn't try to change sql. it just makes it vastly more ergonomic to write

	▲	mattmanser 5 days ago \| parent [-]
		That's pretty much identical to an ORM: `db.Users .Inlude(u => Posts) .Select(u => new { u.Id, Count = u.Posts.Count()});`