The BOOLEAN data type in SQL is designed to represent logical values, making it easy to store and query information that can be either true or false. Valid values for a BOOLEAN column are TRUE, FALSE, and NULL. The NULL value indicates an unknown or missing logical value. BOOLEAN columns are commonly used to track the presence or absence of a condition, such as whether a record is active, a user is verified, or a post is published.

CREATE TABLE blog_posts (
    id SERIAL PRIMARY KEY,
    title VARCHAR(255) NOT NULL,
    content TEXT NOT NULL,
    is_published BOOLEAN
);

In the example above, the is_published column uses the BOOLEAN type to indicate whether a blog post is published. Behind the scenes, many SQL databases store BOOLEAN values as integers, where TRUE is often stored as 1, FALSE as 0, and NULL remains as NULL. When you query a BOOLEAN column, you can use the keywords TRUE, FALSE, or check for NULL to filter results. This makes it straightforward to write queries that target specific logical conditions, such as finding all published blog posts.

-- Insert some blog posts with different published statuses
INSERT INTO blog_posts (title, content, is_published) VALUES
('First Post', 'Welcome to the blog!', TRUE),
('Draft Post', 'This is a draft.', FALSE),
('Mystery Post', 'Status unknown.', NULL);

-- Select all published posts
SELECT title FROM blog_posts WHERE is_published = TRUE;

-- Select posts that are not published
SELECT title FROM blog_posts WHERE is_published = FALSE;

-- Select posts where the published status is unknown
SELECT title FROM blog_posts WHERE is_published IS NULL;