Select columns across different databases

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;

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;

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;

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;

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;

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

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 });

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)

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

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});