SQL Cheatsheet

Essential SQL commands and operations

Page content

Here’s a concise SQL cheatsheet covering essential commands and operations:

sql in a gothic cathedral

Querying Data

Select specific columns:

SELECT column1, column2 FROM table_name;

Select all columns:

SELECT * FROM table_name;

Select distinct values:

SELECT DISTINCT column1, column2 FROM table_name;

Filter data:

SELECT column1, column2 FROM table_name WHERE condition;

Sort data:

SELECT column1, column2 FROM table_name ORDER BY column1 [ASC|DESC];

Modifying Data

Insert a row:

INSERT INTO table_name (column1, column2) VALUES (value1, value2);

Update data:

UPDATE table_name SET column1 = value1 WHERE condition;

Delete data:

DELETE FROM table_name WHERE condition;

Table Operations

Create a table:

CREATE TABLE table_name (
    column1 datatype PRIMARY KEY,
    column2 datatype,
    column3 datatype
);

Alter a table:

ALTER TABLE table_name ADD column_name datatype;

Drop a table:

DROP TABLE table_name;

Joins

Inner join:

SELECT * FROM table1 INNER JOIN table2 ON table1.column = table2.column;

Left join:

SELECT * FROM table1 LEFT JOIN table2 ON table1.column = table2.column;

Aggregation Functions

Count rows:

SELECT COUNT(*) FROM table_name;

Sum values:

SELECT SUM(column_name) FROM table_name;

Average values:

SELECT AVG(column_name) FROM table_name;

Group by:

SELECT column1, COUNT(*) FROM table_name GROUP BY column1;

Subqueries

Subquery in SELECT:

SELECT column1, (SELECT AVG(column2) FROM table2) AS avg_col2 FROM table1;

Subquery in WHERE:

SELECT * FROM table1 WHERE column1 IN (SELECT column2 FROM table2);

Views

Create a view:

CREATE VIEW view_name AS SELECT column1, column2 FROM table_name WHERE condition;

Drop a view:

DROP VIEW view_name;

This cheatsheet covers the most commonly used SQL commands and operations.

It provides a quick reference for querying, modifying data, table operations, joins, aggregation functions, subqueries, and views.

Inner vs Outer joins

INNER JOIN and LEFT JOIN are two common types of SQL join operations that differ in how they combine data from multiple tables:

  1. INNER JOIN:

    • Returns only the rows where there is a match in both tables based on the join condition.
    • Excludes any rows that don’t have a corresponding match in the other table.
    • Useful when you want to retrieve only data that exists in both tables.

  2. LEFT JOIN (also called LEFT OUTER JOIN):

    • Returns all rows from the left table and the matched rows from the right table.
    • If there’s no match in the right table, it still includes the row from the left table and fills in NULL values for the right table’s columns.
    • Useful when you want to see all records from the left table, even if they don’t have corresponding data in the right table.

Key Differences:

  1. Result Set:

    • INNER JOIN: Only returns matching rows.
    • LEFT JOIN: Returns all rows from the left table, plus matching rows from the right table.

  2. NULL Values:

    • INNER JOIN: Does not produce NULL values for unmatched rows.
    • LEFT JOIN: Produces NULL values for columns from the right table when there’s no match.

  3. Use Cases:

    • INNER JOIN: When you need data that exists in both tables.
    • LEFT JOIN: When you need all data from one table and any matching data from another.

  4. Data Completeness:

    • INNER JOIN: May exclude data if there are no matches.
    • LEFT JOIN: Ensures all data from the left table is included, providing a more complete view of the left table’s data.

Understanding these differences allows you to choose the appropriate join type based on your specific data retrieval needs and analysis goals.