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

CodePudding user response：

you could use A join statement to combine the two tables. And use a concat function to join the author name columns

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