DDL, DML, DQL, DCL — the four SQL sublanguages

Beginner5 min read·eng-15-003

sqlinterview

Concept

DDL, DML, DQL, DCL — the four sublanguages of SQL, each handling a different concern.

DDL (Data Definition Language): Defines the STRUCTURE of the database. Creates, alters, drops schemas, tables, indexes, constraints.

CREATE TABLE, ALTER TABLE, DROP TABLE, CREATE INDEX, TRUNCATE
Changes are auto-committed in most databases (no rollback).

DML (Data Manipulation Language): Manipulates the DATA within tables.

INSERT, UPDATE, DELETE
Transactional — can be rolled back.
UPSERT (INSERT ... ON DUPLICATE KEY UPDATE in MySQL, INSERT ... ON CONFLICT DO UPDATE in PostgreSQL).

DQL (Data Query Language): Queries and retrieves data. Sometimes considered part of DML.

SELECT — the only DQL statement.
Includes JOIN, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT.

DCL (Data Control Language): Controls access permissions.

GRANT permission ON table TO user
REVOKE permission ON table FROM user
Manages who can do what.

Why the classification matters:

Transactions: DML and DQL are transactional. DDL in most databases auto-commits (exception: PostgreSQL supports DDL in transactions!).
Migration tools: Migrations use DDL to evolve schema.
ORM layers: Eloquent generates DML (INSERT, UPDATE, DELETE) and DQL (SELECT). Schema::create() generates DDL.
Audit logging: You might log DML but not DQL.

Code Example

php

<?php
// DDL — Schema definition
// In Laravel migrations:
\Schema::create('orders', function (\Illuminate\Database\Schema\Blueprint $table) {
    $table->id();                               // CREATE TABLE orders (id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    $table->foreignId('user_id');               //  user_id BIGINT UNSIGNED NOT NULL,
    $table->decimal('total', 10, 2);            //  total DECIMAL(10,2) NOT NULL,
    $table->enum('status', ['pending','paid']); //  status ENUM('pending','paid') NOT NULL,
    $table->timestamps();                       //  created_at TIMESTAMP, updated_at TIMESTAMP
    $table->index('user_id');                   // CREATE INDEX orders_user_id_index ON orders (user_id)
});

\Schema::table('orders', function ($table) {    // ALTER TABLE orders
    $table->string('tracking_number')->nullable(); // ADD COLUMN tracking_number VARCHAR(255) NULL
});

\Schema::dropIfExists('orders');                // DROP TABLE IF EXISTS orders

// DDL raw
\DB::statement('CREATE INDEX idx_orders_status ON orders (status)');

// DML — Manipulating data
\DB::table('orders')->insert([                  // INSERT INTO orders (user_id, total, ...) VALUES (1, 99.99, ...)
    'user_id' => 1, 'total' => 99.99, 'status' => 'pending',
    'created_at' => now(), 'updated_at' => now(),
]);

\DB::table('orders')->where('id', 1)->update(['status' => 'paid']); // UPDATE orders SET status = 'paid' WHERE id = 1
\DB::table('orders')->where('id', 1)->delete();                      // DELETE FROM orders WHERE id = 1

// UPSERT (DML) — INSERT or UPDATE
\DB::table('settings')->upsert(
    [['key' => 'theme', 'value' => 'dark', 'user_id' => 1]],
    ['key', 'user_id'],         // conflict columns
    ['value'],                  // columns to update on conflict
);
// MySQL:    INSERT INTO settings ... ON DUPLICATE KEY UPDATE value = VALUES(value)
// Postgres: INSERT INTO settings ... ON CONFLICT (key, user_id) DO UPDATE SET value = EXCLUDED.value

// DQL — Querying
$orders = \DB::table('orders')                  // SELECT o.*, u.name
    ->join('users as u', 'orders.user_id', 'u.id')
    ->where('orders.status', 'pending')
    ->select('orders.*', 'u.name as user_name')
    ->orderBy('orders.created_at', 'desc')
    ->limit(10)
    ->get();

// DCL — Permissions (raw SQL, not commonly done in PHP apps)
\DB::statement("GRANT SELECT, INSERT ON orders TO 'api_user'@'%'");
\DB::statement("REVOKE DELETE ON orders FROM 'api_user'@'%'");

PreviousActiveRecord vs DataMapper — two ORM patterns, different trade-offsVocabulary — Database & SQL NextSchema — the structure definition of a database (tables, columns, types)Vocabulary — Database & SQL