Introduction
Selecting specific columns instead of SELECT * is a fundamental practice for database performance, bandwidth reduction, and API clarity. While the basic SELECT syntax is similar across SQL databases, each database system has unique features for column selection — computed columns, JSON extraction, column aliasing, and cross-database queries. NoSQL databases like MongoDB and DynamoDB have their own projection mechanisms entirely.
SQL Databases: Basic Column Selection
The core syntax is the same across MySQL, PostgreSQL, SQL Server, and SQLite:
1-- Select specific columns
2SELECT first_name, last_name, email
3FROM users
4WHERE active = true;
5
6-- Alias columns
7SELECT first_name AS name, email AS contact
8FROM users;
9
10-- Computed columns
11SELECT first_name, last_name,
12 first_name || ' ' || last_name AS full_name
13FROM users;
MySQL
1-- Backticks for reserved words or special characters
2SELECT `name`, `order`, `group`
3FROM products;
4
5-- CONCAT for string concatenation
6SELECT CONCAT(first_name, ' ', last_name) AS full_name
7FROM users;
8
9-- IF/CASE for conditional columns
10SELECT name,
11 IF(price > 100, 'expensive', 'affordable') AS category
12FROM products;
13
14-- JSON column extraction (MySQL 5.7+)
15SELECT id, data->>'$.name' AS name, data->>'$.email' AS email
16FROM users;
PostgreSQL
1-- Double quotes for case-sensitive identifiers
2SELECT "firstName", "lastName"
3FROM users;
4
5-- String concatenation with ||
6SELECT first_name || ' ' || last_name AS full_name
7FROM users;
8
9-- Array column access
10SELECT tags[1] AS first_tag
11FROM articles;
12
13-- JSONB column extraction
14SELECT id, profile->>'name' AS name, profile->'address'->>'city' AS city
15FROM users;
16
17-- Lateral column alias (can reference earlier aliases)
18SELECT price, price * 0.1 AS tax, price * 1.1 AS total
19FROM products;
20
21-- DISTINCT ON (PostgreSQL-specific)
22SELECT DISTINCT ON (category) category, name, price
23FROM products
24ORDER BY category, price DESC;
SQL Server
1-- Square brackets for reserved words
2SELECT [name], [order], [group]
3FROM products;
4
5-- String concatenation with +
6SELECT first_name + ' ' + last_name AS full_name
7FROM users;
8
9-- TOP instead of LIMIT
10SELECT TOP 10 first_name, last_name
11FROM users;
12
13-- Cross-database query (same server)
14SELECT a.name, b.order_total
15FROM DatabaseA.dbo.users a
16JOIN DatabaseB.dbo.orders b ON a.id = b.user_id;
17
18-- Linked server (different server)
19SELECT a.name, b.order_total
20FROM users a
21JOIN [RemoteServer].[DatabaseB].[dbo].[orders] b ON a.id = b.user_id;
22
23-- JSON extraction (SQL Server 2016+)
24SELECT id, JSON_VALUE(data, '$.name') AS name
25FROM users;
SQLite
1-- Simple column selection
2SELECT name, email FROM users;
3
4-- String concatenation with ||
5SELECT first_name || ' ' || last_name AS full_name
6FROM users;
7
8-- JSON extraction (SQLite 3.38+)
9SELECT id, json_extract(data, '$.name') AS name
10FROM users;
11
12-- LIMIT and OFFSET
13SELECT name, email
14FROM users
15LIMIT 10 OFFSET 20;
Cross-Database Queries in SQL
Querying across different databases on the same server:
1-- MySQL: Use database.table syntax
2SELECT a.name, b.order_total
3FROM db1.users a
4JOIN db2.orders b ON a.id = b.user_id;
5
6-- PostgreSQL: Use schemas (not cross-database)
7SELECT a.name, b.order_total
8FROM public.users a
9JOIN sales.orders b ON a.id = b.user_id;
10
11-- PostgreSQL cross-database: Use dblink or Foreign Data Wrappers
12-- Install the extension
13CREATE EXTENSION dblink;
14
15SELECT *
16FROM dblink('dbname=other_db', 'SELECT id, name FROM users')
17AS remote_users(id int, name text);
18
19-- Foreign Data Wrapper (more permanent)
20CREATE SERVER remote_server FOREIGN DATA WRAPPER postgres_fdw
21OPTIONS (host 'remote-host', dbname 'other_db');
22
23CREATE FOREIGN TABLE remote_users (id int, name text)
24SERVER remote_server OPTIONS (table_name 'users');
25
26SELECT * FROM remote_users; -- Queries the remote database
MongoDB (NoSQL)
MongoDB uses projection to select fields:
1// Select specific fields (1 = include, 0 = exclude)
2db.users.find(
3 { active: true },
4 { first_name: 1, email: 1, _id: 0 }
5);
6
7// Aggregation pipeline with $project
8db.users.aggregate([
9 { $match: { active: true } },
10 { $project: {
11 full_name: { $concat: ["$first_name", " ", "$last_name"] },
12 email: 1,
13 _id: 0
14 }}
15]);
16
17// Nested field selection
18db.users.find({}, { "address.city": 1, "address.zip": 1 });
DynamoDB
1import boto3
2
3dynamodb = boto3.resource('dynamodb')
4table = dynamodb.Table('Users')
5
6# ProjectionExpression for column selection
7response = table.scan(
8 ProjectionExpression='first_name, email, address.city',
9 FilterExpression='active = :val',
10 ExpressionAttributeValues={':val': True}
11)
Redis
1# Redis stores key-value pairs — no column concept
2# For hash fields, use HGET or HMGET
3HMGET user:1 name email city
4
5# Get all fields
6HGETALL user:1
ORM Examples
1# SQLAlchemy (Python)
2session.query(User.first_name, User.email).filter(User.active == True).all()
3
4# Django ORM
5User.objects.filter(active=True).values('first_name', 'email')
6User.objects.filter(active=True).values_list('first_name', 'email')
7
8# Sequelize (Node.js)
9User.findAll({
10 attributes: ['first_name', 'email'],
11 where: { active: true }
12});
Common Pitfalls
SELECT * in production: SELECT * retrieves all columns, including large text/blob fields you may not need. It wastes bandwidth, prevents covering index optimization, and breaks when columns are added or renamed. Always specify columns.
Column name conflicts in JOINs: When joining tables with same-named columns (e.g., both have id), use aliases: SELECT u.id AS user_id, o.id AS order_id.
Cross-database query permissions: Querying across databases requires permissions on both databases. In SQL Server, linked servers require separate authentication configuration.
Case sensitivity varies: MySQL column names are case-insensitive on most systems, PostgreSQL is case-insensitive unless quoted ("Name" vs name), SQL Server is case-insensitive by default but configurable.
MongoDB projection mixing: You cannot mix inclusion and exclusion in the same projection (except for _id). { name: 1, email: 0 } is an error — use either { name: 1 } or { email: 0 }.
Summary
Use explicit column names instead of SELECT * for performance and clarity
MySQL uses backticks for identifiers, PostgreSQL uses double quotes, SQL Server uses square brackets
Cross-database queries: MySQL uses db.table, SQL Server uses db.schema.table or linked servers, PostgreSQL uses Foreign Data Wrappers
MongoDB uses projection objects ({ field: 1 }) instead of SELECT
ORM equivalents: .values() (Django), query(Model.col) (SQLAlchemy), attributes (Sequelize)
