SQL Combine data from two tables

Question

How to combine two tables in SQL?

Suppose we have table called books

book_id    author_id       name
_______    _________       _____________
1          2               XYZ
2          1               ABC

And we have table called authors

author_id        firstname        surname
___________      ____________     ___________
1                Alex             Woodman
2                Steve            Bush

I want to combine books and authors in select query:

book_id       author_id     name          author_name
_________     __________    __________    ______________
1             2             XYZ           Steve Bush
2             1             ABC           Alex Woodman

Answer 1

You could use the JOIN clause to merge the two tables and use the CONCAT function to concatenate name and surname of the author:

SELECT 
    b.book_id, 
    a.author_id, 
    b.name, 
    CONCAT(a.firstname, ' ', a.surname) AS author_name
FROM 
    books b 
JOIN 
    author a ON b.author_id = a.author_id