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.

• The FROM keyword is used in SQL to specify the table from which data should be selected or deleted.
• It is an essential component of SQL statements, particularly SELECT, DELETE, UPDATE and some other DML (Data Manipulation Language) operations.

Basic Syntax:

The basic syntax of the FROM keyword in a SELECT the statement is as follows:

SELECT column1, column2, ...
FROM table_name;

Explanation:

• column1, column2, ... are the columns you want to retrieve.
• table_name is the name of the table from which to retrieve the data.

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 |

1. When working with multiple tables, using aliases makes your SQL statements easier to read and maintain.
2. Instead of using SELECT *, specify the columns you need. This can improve performance and readability.
3. Use the appropriate type of join based on your requirement to ensure you get the correct results.
4. Use the WHERE clause to filter data as early as possible to reduce the amount of data processed by subsequent operations.

In conclusion, the SQL FROM keyword is a fundamental aspect of SQL queries, enabling users to specify the source table for data operations. Whether selecting data from a single table, joining multiple tables, or using subqueries, the FROM keyword is essential for building powerful and efficient SQL statements.