SQL Mindset for Programmers

[tomerbd]

I became much better in Sql over the recent two weeks. I learned a few important things that just blocked me from being able to write Sql before.

The main thing I needed to change mindset from programming in java style my brain just couldn't fit the Sql mindset.

Specifically:

1. Think of new columns as variables.

2. Think of new tables as variables.

3. Think of Sql as functions take some input transform put output in a new column (variable) or new table. And less like a magical big bulk of Sql to pull many data many tables join and output, no, split to functions, to smaller Sql just like functions.

4. When writing Sql try to think first as a sequence of functions, doThis, doThat, doThis, then transform each of them to a small Sql query, each result is in a new column, or a new table.

With the above steps I can start thinking about how to write Sql that I need, before getting the above concept I had a block, I just could not get how to approach Sql.

[deeteecee]

I mean, i think you just need practice. i feel like you're thinking too literally about the basic process of programming and trying to apply it to sql.

Regarding table joins, this visual with venn diagrams on https://stackoverflow.com/questions/17946221/sql-join-and-di... usually helps me. But again, it's just practice. Try to think of real world cases and then creating relevant join statements for those.

[cafard]

I'm not entirely sure I follow this. However, you might find it convenient to employ "common table expressions" (CTEs) in your queries:

  WITH cte_a AS
  (SELECT ... FROM something WHERE ...),
  cte_b AS
  (SELECT ... FROM something_else JOIN cte_a ON ... WHERE ...),
  ...
  SELECT whatever 
  FROM cte_z
     JOIN another_table ON ...
  WHERE ...

[HelloNurse]

Do you know you can write actual functions in SQL?

Oracle, in particular, has several ways to return table-like objects from functions.

[tomerbd → HelloNurse]

I didn't know that, thanks. I will check it out specifically for postgresql I see - https://www.postgresql.org/docs/9.5/sql-createfunction.html