Learn SQLite in Neovim: Selecting Columns — Pick Only What You Need | Episode 3

Learn SQLite in Neovim: Selecting Columns — Pick Only What You Need

When querying a database, avoid using SELECT * to fetch all columns, as it can lead to unnecessary data retrieval and slower performance. Instead, specify exactly which columns you want to see, controlling both the output content and order. This approach results in cleaner, more efficient queries and easier-to-read results.

Code

-- Create a table named books with columns for title, author, and pages
CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT,
  author TEXT,
  pages INTEGER
);

-- Insert five sample books into the table
INSERT INTO books (title, author, pages) VALUES
  ('1984', 'George Orwell', 328),
  ('To Kill a Mockingbird', 'Harper Lee', 281),
  ('The Great Gatsby', 'F. Scott Fitzgerald', 180),
  ('Pride and Prejudice', 'Jane Austen', 279),
  ('Moby-Dick', 'Herman Melville', 635);

-- Select all columns (not recommended for large tables)
SELECT * FROM books;

-- Select only title and author columns
SELECT title, author FROM books;

-- Select title and pages columns
SELECT title, pages FROM books;

-- Select author, title, and pages in a specific order
SELECT author, title, pages FROM books;

-- Select a single column: title only
SELECT title FROM books;

Key Points

• Always list specific column names after SELECT instead of using * to improve query efficiency.
• The order of columns in the output matches the order you specify in the SELECT statement.
• Selecting only needed columns reduces data transferred and speeds up query execution.
• You can run SQL scripts directly from Neovim using commands like :!sqlite3 to execute files.