SQL FROM Keyword Example
Lets understand SQL From with examples. Let’s say we have a some tables called students, courses and enrollments as shown below:
-- Creating the database
CREATE DATABASE school;
-- Using the newly created database
USE school;
-- Creating the students table
CREATE TABLE students (
student_id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(100),
age INT,
birth_date DATE
);
-- Inserting sample data into the 'students' table
INSERT INTO students (name, age, birth_date) VALUES
('John', 20, '2001-05-10'),
('Alice', 18, '2003-02-15'),
('Bob', 22, '1999-10-20');
-- Creating the courses table
CREATE TABLE courses (
course_id INT PRIMARY KEY AUTO_INCREMENT,
course_name VARCHAR(100)
);
-- Inserting sample data into the 'courses' table
INSERT INTO courses (course_name) VALUES
('Mathematics'),
('Physics'),
('Chemistry');
-- Creating the enrollments table to associate students with courses
CREATE TABLE enrollments (
enrollment_id INT PRIMARY KEY AUTO_INCREMENT,
student_id INT,
course_id INT,
FOREIGN KEY (student_id) REFERENCES students(student_id),
FOREIGN KEY (course_id) REFERENCES courses(course_id)
);
-- Inserting sample data into the 'enrollments' table
INSERT INTO enrollments (student_id, course_id) VALUES
(1, 1),
(1, 2),
(2, 1),
(3, 3);
Courses Table
| student_id | name | age | birth_date |
|------------|-------|-----|------------|
| 1 | John | 20 | 2001-05-10 |
| 2 | Alice | 18 | 2003-02-15 |
| 3 | Bob | 22 | 1999-10-20 |
Enrollments Table
| course_id | course_name |
|-----------|-------------|
| 1 | Mathematics |
| 2 | Physics |
| 3 | Chemistry |
Students Table
| enrollment_id | student_id | course_id |
|---------------|------------|-----------|
| 1 | 1 | 1 |
| 2 | 1 | 2 |
| 3 | 2 | 1 |
| 4 | 3 | 3 |
Query 1. Selecting Data from a Single Table
To retrieve data from a single table (employees), we use the FROM
keyword to specify the table name. Here is an example:
SELECT name, age
FROM students;
Output:
| name | age |
|-------|-----|
| John | 20 |
| Alice | 18 |
| Bob | 22 |
Query 2. Selecting Data from Multiple Tables
The FROM
keyword can also be used to select data from multiple tables, often in conjunction with joins. Here is an example using an INNER JOIN
:
SELECT students.name, courses.course_name
FROM students
INNER JOIN enrollments ON students.student_id = enrollments.student_id
INNER JOIN courses ON enrollments.course_id = courses.course_id;
Output:
| name | course_name |
|-------|-------------|
| John | Mathematics |
| John | Physics |
| Alice | Mathematics |
| Bob | Chemistry |
Query 3. Subqueries
The FROM
keyword can also be used with subqueries to create more complex queries. A subquery is a query nested within another query. Here is an example:
SELECT name
FROM (SELECT name, age FROM students WHERE age > 18) AS adult_students;
Output:
| name |
|-------|
| John |
| Bob |
Query 4. Using FROM with DELETE and UPDATE
The FROM
keyword is not only used with SELECT
statements but also with DELETE
and UPDATE
statements to specify the table to be modified.
DELETE Example:
DELETE FROM students
WHERE age < 18;
Output:
| student_id | name | age | birth_date |
|------------|-------|-----|------------|
| 1 | John | 20 | 2001-05-10 |
| 3 | Bob | 22 | 1999-10-20 |
This query deletes records from the students
table where the age is less than 18.
UPDATE Example:
UPDATE students
SET age = age + 1
FROM students
WHERE birth_date = '2000-01-01';
Output:
| student_id | name | age | birth_date |
|------------|-------|-----|------------|
| 1 | John | 20 | 2001-05-10 |
| 2 | Alice | 18 | 2003-02-15 |
| 3 | Bob | 22 | 1999-10-20 |
SQL FROM keyword
The SQL FROM keyword is a crucial component of SQL queries, used to specify the table from which data should be selected or manipulated. It plays a vital role in SELECT, DELETE, UPDATE and other DML operations, allowing users to interact with databases effectively. Understanding the FROM keyword is good for anyone working with SQL databases as it is the foundation for querying and modifying data.
In this article, We will learn about What is the SQL FROM Keyword along with various SQL FROM Keyword Examples and so on.
Contact Us