Ravindra BagaleCourses & study guides मराठी Track your progress

10. MySQL Part 1: Install, Create, Insert and SELECT

10.9 JOINs

By Last updated:

Data vegalya tables madhe aahe – student che naav students madhe, course che naav courses madhe, score enrollments madhe. JOIN te common column var jodto.

  INNER JOIN   only matching rows in both tables
  LEFT JOIN    all rows from the left table + matches (NULL if none)
  RIGHT JOIN   all rows from the right table + matches
  CROSS JOIN   every row with every row (rarely needed)
  SELF JOIN    a table joined with itself

-- INNER: who is enrolled in what, with score
SELECT s.name, c.course_name, e.score
FROM enrollments e
JOIN students s ON s.student_id = e.student_id
JOIN courses  c ON c.course_id  = e.course_id
ORDER BY s.name;

-- LEFT: all students, even those in no course (Raja shows NULL)
SELECT s.name, e.course_id
FROM students s
LEFT JOIN enrollments e ON e.student_id = s.student_id;

-- students who have NO enrollment
SELECT s.name FROM students s
LEFT JOIN enrollments e ON e.student_id = s.student_id
WHERE e.enroll_id IS NULL;

-- RIGHT: all courses, even with no students
SELECT c.course_name, e.student_id
FROM enrollments e
RIGHT JOIN courses c ON c.course_id = e.course_id;

-- SELF JOIN: pairs of students from the same city
SELECT a.name, b.name, a.city
FROM students a JOIN students b ON a.city = b.city AND a.student_id < b.student_id;

MySQL has no FULL OUTER JOIN; combine a LEFT and a RIGHT JOIN with UNION when you need one.

Why this matters for security

UNION lets one query append the results of another – attackers use UNION SELECT username, password FROM users inside a vulnerable query to pull data from a completely different table. Knowing that UNION needs the same number of columns explains the ORDER BY trick from section 10.6.

Ravindra Bagale's Tip

If you forget the ON condition, MySQL joins every row to every row (a cross join), and you get 10 × 12 = 120 rows – students think the data has doubled. After writing a JOIN, always check the ON and the row count of the result.

Practice task

List each course with the number of students enrolled (include courses with zero), and list students who are enrolled in more than one course.

Extended teaching notes

Why this lesson matters

Use this lesson to strengthen systems you own: understand the risk, practise safely in a lab, then write and apply the fix.

Apply the fix across stacks

Same class of bug, same idea in every language.

Java / Spring Boot

Parameterise queries, encode output, CSRF tokens, limited Actuator, dependency scanning.

ASP.NET Core

EF Core / parameterised SQL, Razor encoding, HTTPS, Data Protection.

Node.js / Express

helmet, parameterised SQL, npm audit, avoid eval and shell concat.

Python / Django / Flask / FastAPI

ORM, autoescape, pip-audit, Bandit; never shell=True with user input.

PHP / Laravel

PDO/Eloquent, Blade escaping, composer audit.

Go / Ruby

database/sql placeholders / ActiveRecord; html/template / ERB escaping; govulncheck / Brakeman.

nginx / Apache / Tomcat

Hide versions, TLS, security headers, least-open ports.

MySQL / PostgreSQL / MongoDB / Redis

Private networks, auth on, least-privilege users, no 0.0.0.0/0.

AWS / Azure / GCP

Roles not long-lived keys, private data stores, cloud audit logs on.

Mini case study

Suppose we are on a purple-team exercise for an Indian mid-size company (sample data). Red notes a technique from this lesson against a lab clone. Blue must show the control that stops it, the log that would have fired, and the ticket that hardens production. That is the standard this course trains you for.

Defender's worksheet (do this today)

  1. Write the lesson title, date and the exact lab target you are allowed to use.
  2. List three owned systems (VMs, apps, or cloud accounts) where this lesson applies.
  3. Run one safe check from the lesson on a lab system only. Paste the command and output.
  4. Write a two-sentence fix you would give a teammate.
  5. Name one related public advisory (CERT-In, CISA, NVD, or a vendor bulletin) with its date.

Indian workplace (sample data)

Suppose we are a junior analyst at Infosys, TCS, Wipro or a fintech SOC in Bengaluru/Pune. Turn this lesson into one checklist item with an owner, a due date and a place to store evidence (ticket + screenshot).

Global practice

Compare your notes with OWASP, CIS Benchmarks, NIST CSF/IR guidance or a cloud Well-Architected security question. Write five lines on what overlaps.

Everyday analogy

Explain the risk and the fix to a family member without jargon. If they can repeat the warning, you understand it well enough to teach juniors.

Key terms

  • Control: a safeguard (patch, config, process, detection).
  • Finding: a weakness backed by evidence.
  • Remediation: the fix plus how you verified it.
  • Scope: systems you have written permission to touch.
  • Least privilege: only the access the task needs.
  • Defence in depth: several layers so one failure does not end the story.

Common mistakes

  • Skipping written permission "just for a quick scan".
  • Practising on hostel, office or public targets without authorisation.
  • Collecting tool output without writing the fix.
  • Copying attack steps onto production to "verify". Defenders verify with versions, configs and patches.
  • Publishing screenshots that contain secrets, cookies or internal hostnames.

Recap

  • Evidence, fix, owner, date.
  • Own lab only.
  • Fix > spectacle.