You are currently viewing SQL 1.24 SQL INNER JOIN

SQL 1.24 SQL INNER JOIN

Certainly! Let’s delve into the basics of SQL and specifically focus on the INNER JOIN operation. SQL (Structured Query Language) is a powerful tool used for managing and manipulating relational databases. The INNER JOIN operation is fundamental in SQL as it allows you to combine rows from two or more tables based on a related column between them.

What is INNER JOIN?

INNER JOIN is used to retrieve records from two or more tables where there is a match between the columns in those tables. It returns only the rows where there is at least one match in both tables.

Syntax of INNER JOIN:

The basic syntax of INNER JOIN in SQL is:

SELECT column1, column2, ...
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

Example Scenario:

Let’s say we have two tables:

  1. students – containing information about students including their ID and name.
  2. scores – containing information about students’ scores including their ID and score.

We want to retrieve the names of students along with their corresponding scores.

Step-by-Step Explanation:

  1. Creating Tables:

First, let’s create the tables students and scores:

CREATE TABLE students (
    id INT PRIMARY KEY,
    name VARCHAR(50)
);

CREATE TABLE scores (
    student_id INT,
    score INT
);
  1. Inserting Data:

Next, let’s insert some sample data into these tables:

INSERT INTO students (id, name)
VALUES (1, 'Alice'),
       (2, 'Bob'),
       (3, 'Charlie');

INSERT INTO scores (student_id, score)
VALUES (1, 85),
       (2, 90),
       (3, 75);
  1. Performing INNER JOIN:

Now, let’s perform an INNER JOIN to retrieve the names of students along with their scores:

SELECT students.name, scores.score
FROM students
INNER JOIN scores ON students.id = scores.student_id;

This query joins the students table with the scores table based on the id column in students and the student_id column in scores. It then selects the name column from students and the score column from scores.

Output:

|   name  | score |
|---------|-------|
|  Alice  |  85   |
|   Bob   |  90   |
| Charlie |  75   |

Explanation:

  • We use the INNER JOIN keyword to specify that we want to combine rows from both tables where there is a match.
  • The ON keyword is used to specify the condition for joining the tables, in this case, matching the id from the students table with the student_id from the scores table.
  • The SELECT statement is used to specify the columns we want to retrieve from the resulting joined table.

This is a basic example of using INNER JOIN in SQL. INNER JOIN is a powerful tool for combining data from multiple tables based on related columns, enabling you to extract meaningful information from your database.

Leave a Reply