What Are Joins in SQL? | SQL Joins Explained with Examples

Introduction

In SQL, text data types are used to store alphanumeric values like names, addresses, emails, and descriptions. Choosing the correct text type — CHAR, VARCHAR, or TEXT — is important for optimizing storage space, query speed, and database performance.

In this section, you'll learn the definitions, differences, and best use cases for each text data type.

1. CHAR (Fixed-Length String)

CHAR is used to store fixed-length strings. If the stored string is shorter than the defined length, SQL automatically pads it with spaces to match the specified size.

Features:

• Fixed length
• Fast and predictable performance
• Uses extra storage if the data is often shorter than the specified length

Syntax:

column_name CHAR(length);

length = number of characters (1 to 255 depending on the database system)

Example:

CREATE TABLE countries (
  country_code CHAR(2),
  country_name CHAR(50)
);

country_code like 'US', 'IN', 'UK' will always take 2 characters.

When to Use CHAR:

• Data with a constant size, such as country codes, gender ('M', 'F'), state abbreviations
• Fixed-format fields like credit card types ('VISA', 'MC')
• When exact storage size is known and consistent

2. VARCHAR (Variable-Length String)

VARCHAR stands for Variable Character. It stores variable-length strings, meaning only the actual characters are stored without unnecessary padding.

Features:

• Variable length
• More space-efficient than CHAR for varying-length text
• Slightly slower than CHAR when processing large volumes (because of extra calculations for string lengths)

Syntax:

column_name VARCHAR(length);

length = maximum number of characters allowed

Example:

CREATE TABLE employees (
  first_name VARCHAR(50),
  email VARCHAR(100)
);

Names and emails can vary in length, making VARCHAR ideal.

When to Use VARCHAR:

• Data with unpredictable or variable length
• Names, emails, addresses, and descriptions under 255-65535 characters
• Most general-purpose text fields

3. TEXT (Large Text Field)

TEXT is used to store large amounts of text like long descriptions, blog posts, comments, or articles.

Features:

• Meant for large text storage (up to 65,535 characters for standard TEXT in MySQL)
• Cannot have a default value (in some databases like MySQL)
• TEXT fields are stored outside the main table with a pointer reference
• Different variants exist (TINYTEXT, MEDIUMTEXT, LONGTEXT) for various sizes

Syntax:

column_name TEXT;

Example:

CREATE TABLE articles (
  id INT,
  title VARCHAR(255),
  body TEXT
);

body will store the full article content, which can be very large.

When to Use TEXT:

• Long-form text fields (comments, articles, reviews, reports)
• Data that exceeds normal VARCHAR limits
• When exact storage requirements are unknown or potentially very large

Quick Comparison: CHAR vs VARCHAR vs TEXT

Important Tips

• Use CHAR only when all values will be exactly the same length
• VARCHAR is the best choice for most standard text fields
• Reserve TEXT for content that exceeds VARCHAR limits
• Consider VARCHAR(MAX) in SQL Server for large text that might need indexing
• Be aware that TEXT fields may have limitations on default values and full-text indexing

Introduction

In relational databases, data is often split across multiple tables to avoid duplication and ensure consistency. To retrieve related data from these different tables, SQL uses JOINs.

A JOIN combines rows from two or more tables based on a related column, typically a foreign key.

Why Are Joins Important?

• They allow you to query across multiple tables
• Help establish relationships between datasets
• Essential for building real-world applications like e-commerce sites, HR systems, finance dashboards, etc.

Types of Joins in SQL

SQL provides different types of JOINs depending on how you want to match the data:

1. INNER JOIN – Matching Data in Both Tables

Returns only the rows with matching values in both tables.

Syntax:

SELECT columns
FROM table1
INNER JOIN table2
ON table1.column = table2.column;

Example:

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

Shows only employees who belong to a department.

2. LEFT JOIN (LEFT OUTER JOIN) – All from Left Table + Matches

Returns all rows from the left table, and matched rows from the right table. Unmatched rows from the right are filled with NULL.

Example:

SELECT customers.name, orders.order_date
FROM customers
LEFT JOIN orders
ON customers.id = orders.customer_id;

Includes all customers, even if they have no orders.

3. RIGHT JOIN (RIGHT OUTER JOIN) – All from Right Table + Matches

Returns all rows from the right table, and matched rows from the left table. If there's no match, NULL values are shown for the left table's columns.

Note: Not all databases support RIGHT JOIN. Use LEFT JOIN with reversed table order instead.

4. FULL JOIN (FULL OUTER JOIN) – All Rows from Both Tables

Returns all rows from both tables, matching them where possible. Where there is no match, NULL is used.

SELECT a.col1, b.col2
FROM table_a a
FULL OUTER JOIN table_b b
ON a.id = b.id;

Combines everything, even unmatched rows from both sides.

5. CROSS JOIN – All Combinations (Cartesian Product)

Returns every possible row combination from both tables.

SELECT *
FROM colors
CROSS JOIN sizes;

Useful for generating combinations, like all color-size pairs for a product.

Example Tables: employees and departments

INNER JOIN result:

Join vs Subquery

• JOIN is preferred for combining related data from multiple tables
• Subqueries are useful for single-table filtering or when embedding queries

Conclusion

SQL JOINs are powerful tools that allow you to bring together related data from multiple tables using relationships.