Why Every Developer Should Know the SQL Basics
Saturday, 29 November 2025
I think that as a developer it's really important to understand how data is stored and retrieved. Almost every application needs to store data. There are several ways you can do this but the most popular one is SQL (Structured Query Language). Now some of you reading this might only be responsible for the front-end logic in your team, but having an understanding of how data is stored can help guide the design choices you make. Every developer should have a basic understanding of databases and hopefully this post will give you those tools.
In this post we’ll walk through:
- What SQL is actually doing for you under the hood
- The main database providers you’re likely to meet
- How to read and write common SQL queries with real examples
- How tables, views, indexes, and stored procedures fit together
- Why joins matter and how to reason about them
- How modern SQL Projects (Microsoft.Build.Sql) help you treat databases like code
- How SQL fits naturally into .NET with Dapper and EF Core
By the end, you won’t be a DBA (Database Administrator) but you’ll be able to look at a query, understand what it’s doing and maybe even have an opinion about how to improve it.
What is SQL and Why is it Used?
SQL is a standardised programming language designed for managing and manipulating relational databases.
It allows you to:
- Query data - Retrieve specific information from databases
- Insert, update, and delete records - Modify data as needed
- Define database structures - Create tables, views, and relationships
- Control access - Manage permissions and security
Almost every application needs to store and retrieve data, making SQL knowledge invaluable regardless of your tech stack. There are other alternatives to storing data like NoSQL, files, etc. but those are topics for another day.
SQL Providers Overview
SQL itself is a language standard, but real-world databases are built by vendors that implement (and extend) that standard. The core ideas like tables, rows, columns, joins are the same everywhere, but each provider has its own sweet spots, tooling, and quirks.
Below are the ones you’re most likely to encounter:
-
MySQL
What it is: Popular open-source relational database
Where it shines: Classic web apps, LAMP stacks, many hosted platforms -
Microsoft SQL Server
What it is: Microsoft’s flagship relational database
Where it shines: Enterprise .NET apps, strong tooling in Visual Studio and Azure -
PostgreSQL
What it is: Feature-rich open-source database
Where it shines: Complex queries, JSON support, GIS, and extensibility -
SQLite
What it is: Embedded, file-based database
Where it shines: Desktop apps, mobile apps, small tools, local caches -
Oracle
What it is: Enterprise-grade commercial database
Where it shines: Very large enterprises with heavy compliance and legacy systems
From a developer’s perspective, especially in the .NET world, SQL Server is the most common choice. It integrates well with Azure, Visual Studio/Visual Studio Code/Rider, and .NET libraries like Dapper and EF Core. It is free to use for development and has a pretty good free tier in Azure when it comes to hosting.
Each of these providers supports standard SQL, but also adds provider-specific features (data types, functions, performance tuning options, etc.). Microsoft SQL Server exposes these through T-SQL (Transact-SQL), which is the dialect we’ll focus on in this post. If you get comfortable reading and writing T-SQL, you’ll find it much easier to adapt to other providers later.
Getting Started with SQL
Query Structure
SQL queries follow a logical, almost sentence-like structure. The most common statement is SELECT, which you can read as:
“Select these columns, from this table, where these conditions are true, ordered in this way.”
SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column1;So what is this saying?
- SELECT – which pieces of data you want back (columns)
- FROM – which table (or tables) you’re reading from
- WHERE – an optional filter to narrow down rows
- ORDER BY – how you want the results sorted
Common SQL Commands
Data Query Language (DQL)
These commands are all about reading data.
-- Retrieve all customers
SELECT * FROM Customers;This says: “Give me every column (`*`) and every row from the `Customers` table.” It’s fine for quick exploration, but in real applications you usually want to be more explicit so you don’t return unnecessary data.
-- Retrieve specific columns with filtering
SELECT FirstName, LastName, Email
FROM Customers
WHERE Country = 'UK'
ORDER BY LastName;Here's what happens, step by step:
-
The database starts with all rows in
Customers. -
The
WHERE Country = 'UK'filter narrows it down to customers whoseCountrycolumn exactly matchesUK. -
The
Selectlist says “from those matching rows, only return theFirstName,LastName, andEmailcolumns.” -
ORDER BY LastNamesorts the final result set alphabetically by last name before sending it back to your application.
That’s a pattern you’ll see everywhere: filter first, then project the columns you care about, then order the results for humans.
Data Manipulation Language (DML)
These commands are all about changing data.
Inserting Data
-- Insert a new record
INSERT INTO Customers (FirstName, LastName, Email)
VALUES ('John', 'Doe', 'john.doe@email.com');This creates a brand new row in `Customers`. The column list in parentheses tells SQL which columns you’re setting, and `VALUES` provides the actual values. Any columns not listed here either get `NULL`, a default value, or are computed by the database (like identity keys or timestamps). You can add multiple values at time by adding more groups like this:
-- Insert multiple new records
INSERT INTO Customers (FirstName, LastName, Email)
VALUES ('John', 'Doe', 'john.doe@email.com'),
('Alice', 'Smith', 'alice@smith.com');Updating Data
-- Update existing records
UPDATE Customers
SET Email = 'new.email@email.com'
WHERE CustomerId = 1;This finds rows in `Customers` where `CustomerId = 1` and updates the `Email` column on those rows. The `WHERE` clause is critical: forget it, and you update every customer’s email. This is why basic SQL literacy matters, it's really important that you can recognise immediately when a query is too broad.
Deleting Data
-- Delete records
DELETE FROM Customers
WHERE CustomerId = 1;This removes rows from `Customers` where `CustomerId = 1`. Again, the `WHERE` clause limits the scope. Many teams prefer to use a soft-delete pattern instead (e.g. they set an `IsDeleted` flag with an UPDATE command) so mistakes are easier to recover from.
With all of these manipulation commands it's possible to do more complex operations by doing updates based on SELECTs from other tables, etc., but we'll cover that another day in a more advanced blog post.
Tables, Views, and Stored Procedures
T-SQL extends standard SQL with powerful features for database object creation and management. This is where the different providers change things up a bit. The common language CREATE TABLE is standard but the data types will vary per provider, so for example, DATETIME2 might only exist in the Microsoft SQL Server provider.
Creating Tables
CREATE TABLE Customers (
CustomerId INT IDENTITY(1,1) PRIMARY KEY,
FirstName NVARCHAR(50) NOT NULL,
LastName NVARCHAR(50) NOT NULL,
Email NVARCHAR(100) UNIQUE,
CreatedDate DATETIME2 DEFAULT GETDATE()
);This command is telling SQL to create a new table called Customers. It should have a primary key (unique identifier) called CustomerId and track their basic details (name, email). Now let's cover some of this syntax.
- IDENTITY(1,1) – this says that the field is an identity field that should start at number 1 and increment by 1 for each new record.
-
NVARCHAR(n) – tells the database that this data will be text; the number is how many characters
it can be. For example,
NVARCHAR(50)means 50 characters. This is important because it lets SQL know how much space to allocate — the larger the number, the more space that field will need. - NOT NULL – this says that the field requires a value and cannot be null (though it may be an empty string).
- DATETIME2 – tells SQL that this will be a timestamp value.
- DEFAULT GETDATE() – tells SQL it can set a default value if one is not provided, and that default should be the current timestamp.
Creating Indexes
Indexes improve query performance by allowing faster data retrieval:
-- Non-clustered index on Email
CREATE NONCLUSTERED INDEX IX_Customers_Email
ON Customers (Email);
-- Composite index
CREATE NONCLUSTERED INDEX IX_Customers_Name
ON Customers (LastName, FirstName);Indexes are really important as they can drastically improve performance when retrieving data. However, they can also be a detriment to performance if you have too many or unused ones. When thinking about indexes you should look at your most popular SELECT statements. When you're doing a WHERE clause, if you see you're always filtering by Email, then you should have an index on that column (like the first example) as this will help SQL to retrieve that data more quickly for you. Indexes are really powerful and we'll do a deeper dive into those in another blog post. For now though you just need to understand that they are useful and to be used with care.
Creating Views
Views are virtual tables based on query results. These provide a great way to write reusable blocks of SQL as they can be used in other queries via JOIN's. They help you encapsulate a bit of SQL logic, like this example where we want to always get Active Customers.
CREATE VIEW vw_ActiveCustomers AS
SELECT CustomerId, FirstName, LastName, Email
FROM Customers
WHERE IsActive = 1;Creating Stored Procedures (SPROCS)
Stored procedures encapsulate reusable SQL logic that is executed by the database. These are very powerful but are less used in modern development. Most complex logic is now handled in code rather than SQL. Stored Procedures where once the safest way to interact with a database as they provided some level of protection from SQL Injection attacks (where someone can alter the SQL to do something else). But modern ORM's have eliminated this threat so SPROCS are less useful now. But if you're working on more legacy systems you might see lots of these.
CREATE PROCEDURE sp_GetCustomersByCountry
@Country NVARCHAR(50)
AS
BEGIN
SELECT CustomerId, FirstName, LastName, Email
FROM Customers
WHERE Country = @Country
ORDER BY LastName;
END;Complex SQL with Joins
So now that you know how to create tables and write views, how do you get that data out in one go? This is where joins come into play. There are several types of joins that help retrieve data in different ways. Essentially they allow you to select data from multiple tables at once.
INNER JOIN
An `INNER JOIN` returns only the rows where there is a match in both tables. If a customer has no orders, they won't appear in the results. If an order somehow has no matching customer, it won't appear either.
SELECT c.FirstName, c.LastName, o.OrderDate, o.TotalAmount
FROM Customers c
INNER JOIN Orders o ON c.CustomerId = o.CustomerId;Think of it as the intersection of two sets, you only get data where both sides have something to contribute.
LEFT JOIN
A `LEFT JOIN` (also called `LEFT OUTER JOIN`) returns all rows from the left table, and the matching rows from the right table. If there's no match, the right side columns will be `NULL` (empty).
SELECT c.FirstName, c.LastName, o.OrderDate
FROM Customers c
LEFT JOIN Orders o ON c.CustomerId = o.CustomerId;This is useful when you want to see all customers, even those who haven't placed any orders yet. Customers without orders will still appear, but their `OrderDate` will be `NULL`.
RIGHT JOIN
A `RIGHT JOIN` (also called `RIGHT OUTER JOIN`) is the opposite of a `LEFT JOIN`. It returns all rows from the right table, and the matching rows from the left table. If there's no match, the left side columns will be `NULL`.
SELECT c.FirstName, c.LastName, o.OrderDate, o.TotalAmount
FROM Customers c
RIGHT JOIN Orders o ON c.CustomerId = o.CustomerId;In practice, `RIGHT JOIN` is less common because you can usually achieve the same result by swapping the table order and using a `LEFT JOIN`. Most developers prefer `LEFT JOIN` for consistency.
FULL OUTER JOIN
A `FULL OUTER JOIN` returns all rows from both tables. Where there's a match, you get the combined data. Where there's no match on either side, you get `NULL` for the missing columns.
SELECT c.FirstName, c.LastName, o.OrderDate, o.TotalAmount
FROM Customers c
FULL OUTER JOIN Orders o ON c.CustomerId = o.CustomerId;This gives you the complete picture: customers without orders, orders without customers (if that's possible in your schema), and all the matches in between. It's useful for finding orphaned records or doing reconciliation between datasets.
Multiple Joins
In real applications, you'll often need to join more than two tables. You can chain joins together to pull data from across your schema:
SELECT
c.FirstName,
c.LastName,
o.OrderDate,
p.ProductName,
oi.Quantity
FROM Customers c
INNER JOIN Orders o ON c.CustomerId = o.CustomerId
INNER JOIN OrderItems oi ON o.OrderId = oi.OrderId
INNER JOIN Products p ON oi.ProductId = p.ProductId
WHERE o.OrderDate >= '2024-01-01';This query starts with customers, joins to their orders, then to the line items in each order, and finally to the product details. Each join builds on the previous one, letting you traverse the relationships in your database.
Introduction to Microsoft.Build.Sql Projects
So bringing this back to .Net how do we use SQL in our project? This is where SQL Projects using the `Microsoft.Build.Sql` SDK come into play. This project provides a modern approach to database development. It's Microsoft's latest way to manage SQL projects and lets you define your database in files and importantly allows you to source control your database.
We'll cover the basics at a high level here to get you started. I'll do a more detailed blog in the future on this and Dapper. Don't forget to check out the git repo here for example code.
Key Benefits
- Version control friendly – Track all database changes in source control
- Cross-platform – Works on Windows, macOS, and Linux
- CI/CD integration – Build and deploy databases in pipelines
- Schema comparison – Detect drift between environments
- Refactoring support – Safely rename objects with reference updates
Managing Databases with SQL Projects
Creating a SQL Project
You can install the latest SQL Project templates in dotnet using:
You can install the latest SQL Project templates in dotnet using:
# install SqlPackage CLI
dotnet tool install -g Microsoft.SqlPackage
# install Microsoft.Build.Sql.Templates
dotnet new install Microsoft.Build.Sql.TemplatesThen you can create a new sql project using this command:
# create a new SQL database project
dotnet new sqlproj -n MyDatabaseProjectOnce it's created, you'll see a .sqlproj file that looks something like this:
<Project Sdk="Microsoft.Build.Sql/2.0.0">
<PropertyGroup>
<Name>MyDatabase</Name>
<DSP>Microsoft.Data.Tools.Schema.Sql.Sql160DatabaseSchemaProvider</DSP>
<ModelCollation>1033, CI</ModelCollation>
</PropertyGroup>
</Project>Project Structure
The structure is very similar to a .NET project. It allows you to structure your files however you like, but it's best to break them down into tables, views, etc. You could further break them down with schemas but we'll cover those in another blog post.
MyDatabase/
├── MyDatabase.sqlproj
├── Tables/
│ ├── Customers.sql
│ └── Orders.sql
├── Views/
│ └── vw_ActiveCustomers.sql
├── Stored Procedures/
│ └── sp_GetCustomersByCountry.sql
└── Scripts/
└── PostDeployment.sql
Building and Deploying
The advantage of using this new SQL Project is that you can use your existing dotnet tooling and skills to build and manage the database. You can use dotnet build to compile your database into a dacpac (the format used by SQL Server to understand what changes it needs to make). You can then deploy that to your database using the sqlpackage command
# Build the project
dotnet build
# Create a deployment script
sqlpackage /Action:Script /SourceFile:bin/Debug/MyDatabase.dacpac /TargetConnectionString:"..."Using SQL in .NET with Dapper
So now we have a database and a nice project to manage it, how do we call that in our .NET code? This is where Dapper comes into play, it's a lightweight micro-ORM that extends `IDbConnection` with simple object mapping. It's fast, simple, and gives you full control over your SQL. So it's a really nice way to keep those SQL skills sharp. There are other ORMs like Entity Framework Core that take this even further (we'll touch on this at the end).
Setting Up Dapper
Setting up Dapper is as easy as installing the NuGet package. If you're using Aspire (like the example repo) it's even easier as it will handle setting up connections for you.
// Install the NuGet package
// dotnet add package Dapper
using Dapper;
using Microsoft.Data.SqlClient;Basic Queries
Once you have Dapper installed you can start to use it to query and manipulate your database. It will automatically map the responses from the database into your classes. In these examples we get the data from the Customers table and use that to create a list of Customer objects. You can also see how you would insert data into the database using the ExecuteScalarAsync command. This is a very high level example, you can do a lot more with Dapper. I'll cover this in a follow-up blog post.
public class CustomerRepository
{
private readonly string _connectionString;
public CustomerRepository(string connectionString)
{
_connectionString = connectionString;
}
public async Task> GetAllCustomersAsync()
{
using var connection = new SqlConnection(_connectionString);
return await connection.QueryAsync(
"SELECT CustomerId, FirstName, LastName, Email FROM Customers");
}
public async Task GetCustomerByIdAsync(int id)
{
using var connection = new SqlConnection(_connectionString);
return await connection.QuerySingleOrDefaultAsync(
"SELECT * FROM Customers WHERE CustomerId = @Id",
new { Id = id });
}
public async Task CreateCustomerAsync(Customer customer)
{
using var connection = new SqlConnection(_connectionString);
return await connection.ExecuteScalarAsync(
@"INSERT INTO Customers (FirstName, LastName, Email)
VALUES (@FirstName, @LastName, @Email);
SELECT SCOPE_IDENTITY();",
customer);
}
} Entity Framework Core - A Brief Overview
I think it's important to talk briefly about other options. While Dapper gives you direct SQL control, Entity Framework Core takes a different approach as a full-featured ORM. It allows you to write code in .NET using classes, etc., and it will manage the database in the background for you. This means you don't have to write any SQL or manage tables, etc. EF Core does it all for you.
Key Differences from Dapper
-
Approach
Dapper: Micro-ORM, raw SQL
EF Core: Full ORM, LINQ queries -
Performance
Dapper: Faster, minimal overhead
EF Core: Slightly slower, more features -
Learning curve
Dapper: Simple, SQL knowledge required
EF Core: Steeper, abstracts SQL -
Migrations
Dapper: Manual or SQL Projects
EF Core: Built-in migration system -
Best for
Dapper: Performance-critical, complex queries
EF Core: Rapid development, simple CRUD
When to Choose Each
- Choose Dapper – when you need maximum performance, have complex queries, or want full SQL control
- Choose EF Core – when you want rapid development, built-in change tracking, and don't need to optimise every query
Many projects use both EF Core for simple CRUD operations and Dapper for complex reporting queries. At the end of the day you should use whichever one is easiest for you. Understanding SQL is just as important for both options (even if EF writes it for you) so you can write the most optimised queries possible.
Closing
SQL remains one of the most important skills for developers to master. Understanding the fundamentals empowers you to:
- Write efficient queries that perform well at scale
- Design proper database schemas
- Debug data issues quickly
- Make informed decisions about data access strategies
Whether you choose Dapper, Entity Framework Core, or a combination of both, having solid SQL knowledge will make you a more effective developer. SQL Projects with `Microsoft.Build.Sql` bring modern development practices to database management, making it easier than ever to version control and deploy your database changes.
So are you ready to put these concepts into practice? Check out my introduction repository at sql-dapper-demo to get hands-on experience with:
- SQL fundamentals and query writing
- Setting up SQL Projects with Microsoft.Build.Sql
- Implementing data access with Dapper
- Best practices for .NET database development
Clone the repo, follow along with the examples, and start building your SQL skills today! 🚀
Subscribe so you don't miss out 🚀
Be the first to know about new blog posts, news, and more. Delivered straight to your inbox 📨
