AI Hints
Home›SQL Tutorial›Designing Databases
SQL · Lesson 80 of 126

SQL Dialects

SQLite, Postgres, MySQL, SQL Server.

⏱ 2 min read▶ Video at 1:52:27🎓 Free
Watch this lesson in the SQL courseWatch on YouTube ↗Full SQL course

You've learned SQL with SQLite. But at work, you might use Postgres, MySQL, or Microsoft SQL Server. Here's the good news. About ninety five percent of what you've learned works everywhere. Select, where, group by, joins, CTEs, window functions. All the same. Only a few small details change.

SQL Dialects
From the video: SQL Dialects
  • SQLite, Postgres, MySQL, SQL Server
  • 95% is the same
  • A few small details differ

The main differences

Here are the differences you'll notice first. To get the first ten rows, most use limit, but SQL Server uses top. To join text, most use two pipes, but MySQL uses concat. For dates, SQLite uses strftime, while the others have functions like extract, or year. And automatic IDs have slightly different names.

TaskSQLite / PostgresMySQLSQL Server
First 10 rowsLIMIT 10LIMIT 10TOP 10
Join texta || bCONCAT(a, b)a + b
Year of a datestrftime / EXTRACTYEAR(d)YEAR(d)
Auto IDINTEGER PK / SERIALAUTO_INCREMENTIDENTITY

How to switch

So when you move to a new database, don't panic. Your skills transfer. When something doesn't work, search for the task, plus the database name. For example, Postgres year from date. You'll have the answer in seconds.

Your skillstransfer
Core SQLthe same everywhere
Stuck?search task + database
Examplepostgres year from date

Summary

SQL is a standard, with small differences between products. What you've learned works everywhere. Now, let's put it all to work on real projects.

Key takeaways
  • 95% is the same
  • LIMIT vs TOP
  • Search when stuck
Next up: Project 1: The Monthly Report — a report Helen can use