Query Builder vs Raw SQL

Basic Retrieval

get() → Fetch all results

SQL:

SELECT * FROM employees;

Laravel:

DB::table('employees')->get();

first() → Get single row

SQL:

SELECT * FROM employees LIMIT 1;

Laravel:

DB::table('employees')->first();

pluck() → Get single column values

SQL:

SELECT name FROM employees;

Laravel:

DB::table('employees')->pluck('name');

distinct()

SQL:

SELECT DISTINCT city FROM employees;

Laravel:

DB::table('employees')->distinct()->pluck('city');

Filtering Data (WHERE)

where()

SQL:

SELECT * FROM employees WHERE status = 'active';

Laravel:

DB::table('employees')->where('status', 'active')->get();

orWhere()

SQL:

SELECT * FROM employees WHERE age > 30 OR city = 'Delhi';

Laravel:

DB::table('employees')->where('age', '>', 30)->orWhere('city', 'Delhi')->get();

whereBetween() / whereNotBetween()

SQL:

SELECT * FROM employees WHERE salary BETWEEN 3000 AND 8000;

Laravel:

DB::table('employees')->whereBetween('salary', [3000, 8000])->get();

whereIn() / whereNotIn()

SQL:

SELECT * FROM employees WHERE department_id IN (1,2,3);

Laravel:

DB::table('employees')->whereIn('department_id', [1,2,3])->get();

whereNull() / whereNotNull()

SQL:

SELECT * FROM employees WHERE manager_id IS NULL;

Laravel:

DB::table('employees')->whereNull('manager_id')->get();

Date Conditions

SQL:

SELECT * FROM employees WHERE YEAR(hired_at) = 2023;

Laravel:

DB::table('employees')->whereYear('hired_at', 2023)->get();

Sorting & Limiting

orderBy()

SQL:

SELECT * FROM employees ORDER BY salary DESC;

Laravel:

DB::table('employees')->orderBy('salary', 'desc')->get();

limit() & offset()

SQL:

SELECT * FROM employees LIMIT 5 OFFSET 10;

Laravel:

DB::table('employees')->limit(5)->offset(10)->get();

oldest() & latest()

Laravel Only

DB::table('employees')->oldest()->get();
DB::table('employees')->latest()->get();

take() (alias for limit())

DB::table('employees')->take(3)->get();

Aggregates

SQL:

SELECT COUNT(*) FROM employees;
SELECT MAX(salary) FROM employees;
SELECT AVG(salary) FROM employees;

Laravel:

DB::table('employees')->count();
DB::table('employees')->max('salary');
DB::table('employees')->avg('salary');

Insert Data

insert()

SQL:

INSERT INTO employees (name, email) VALUES ('John', 'john@example.com');

Laravel:

DB::table('employees')->insert([
  'name' => 'John', 'email' => 'john@example.com'
]);

Insert Multiple Records

SQL:

INSERT INTO employees (name, email) VALUES ('John','j@x.com'),('Sam','s@x.com');

Laravel:

DB::table('employees')->insert([
  ['name'=>'John','email'=>'j@x.com'],
  ['name'=>'Sam','email'=>'s@x.com']
]);

insertOrIgnore()

Laravel only (ignore duplicates)

DB::table('employees')->insertOrIgnore([...]);

insertGetId()

Get ID of inserted record

$id = DB::table('employees')->insertGetId([
  'name' => 'Ravi'
]);

upsert()

SQL:

INSERT INTO employees (email, name) VALUES ('a@x.com','A')
ON DUPLICATE KEY UPDATE name='A';

Laravel:

DB::table('employees')->upsert(
  [['email'=>'a@x.com','name'=>'A']],
  ['email'], ['name']
);

Update / Delete

update()

SQL:

UPDATE employees SET status='inactive' WHERE id=5;

Laravel:

DB::table('employees')->where('id', 5)->update(['status'=>'inactive']);

increment() / decrement()

SQL:

UPDATE employees SET salary = salary + 1000;

Laravel:

DB::table('employees')->increment('salary', 1000);

updateOrInsert()

Laravel only

DB::table('employees')->updateOrInsert(
  ['email'=>'a@x.com'], ['name'=>'Updated']
);

delete() & truncate()

SQL:

DELETE FROM employees WHERE id=1;
TRUNCATE TABLE employees;

Laravel:

DB::table('employees')->where('id',1)->delete();
DB::table('employees')->truncate();

Joins

Inner Join

SQL:

SELECT employees.name, departments.name
FROM employees
INNER JOIN departments ON employees.dept_id=departments.id;

Laravel:

DB::table('employees')
  ->join('departments','employees.dept_id','=','departments.id')
  ->select('employees.name','departments.name')
  ->get();

Left / Right / Cross Join

DB::table('employees')->leftJoin(...)->get();
DB::table('employees')->rightJoin(...)->get();
DB::table('employees')->crossJoin(...)->get();

Self Join (alias)

DB::table('employees as e1')
   ->join('employees as e2','e1.manager_id','=','e2.id')
   ->select('e1.name','e2.name as manager')
   ->get();

Nested Joins / joinSub()

DB::table('employees')
  ->joinSub(DB::table('projects')->select('emp_id','count(*) as total'),'p',function($join){
     $join->on('employees.id','=','p.emp_id');
  })->get();

Group By / Having

SQL:

SELECT dept_id, COUNT(*) FROM employees GROUP BY dept_id HAVING COUNT(*) > 5;

Laravel:

DB::table('employees')
   ->select('dept_id', DB::raw('COUNT(*) as total'))
   ->groupBy('dept_id')
   ->having('total','>',5)
   ->get();

Union

SQL:

SELECT name FROM employees
UNION
SELECT name FROM managers;

Laravel:

$first = DB::table('employees')->select('name');
$second = DB::table('managers')->select('name');
$first->union($second)->get();

Raw Expressions

• selectRaw()
• whereRaw()
• havingRaw()
• orderByRaw()
• DB::raw()

DB::table('employees')
  ->selectRaw('count(*) as total, dept_id')
  ->groupBy('dept_id')
  ->havingRaw('total > ?', [5])
  ->get();

Advanced Features

• Subqueries:
• selectSub(), fromSub(), joinSub()
• Grouped Conditions:

DB::table('employees')
  ->where(function($q){
     $q->where('age','>',25)->orWhere('city','Delhi');
  })->get();

• Chunking:

DB::table('employees')->chunk(100, function($records){ ... });

• Streaming with cursor():

foreach(DB::table('employees')->cursor() as $emp){ ... }

• Pagination:

DB::table('employees')->paginate(15);
DB::table('employees')->simplePaginate(15);

• Transactions & Locks:

DB::transaction(function(){ ... });
DB::table('employees')->sharedLock()->get();
DB::table('employees')->lockForUpdate()->get();

• Debugging Queries:

DB::enableQueryLog();
DB::getQueryLog();
DB::table('employees')->toSql();