SELECT 'book' AS result_type
     , Book_ID
     , Title 
     , NULL as Name
  FROM Books 
 WHERE Title LIKE '%word%'
UNION ALL
SELECT 'song' AS result_type
     , Book_ID
     , Title 
     , Name 
  FROM Songs 
 WHERE Name LIKE '%word%'

SELECT * FROM Books;
------------------------------------------------------------------------------

BOOK_ID     TITLE            DESCRIPTIONS                  
----------- ---------------- ------------------------------
          1 Book A           One Song   with         "word"
          2 Book B           No Song                       
          3 Book C           Some Songs with/without "word"
          4 Book is a word   No Song                       
          5 words in Book E  Two Songs  with         "word"
          7 Book G: for word Some Songs      without "word"

  6 record(s) selected.


SELECT * FROM Songs;
------------------------------------------------------------------------------

SONG_ID     BOOK_ID     NAME                     
----------- ----------- -------------------------
        100           1 A word from Song 100     
        200           3 Song 200 word & words    
        300           3 Song 300 has a word in it
        400           3 Song 400 work            
        500           3 Song 500 has some words  
        600           5 Song 600 has a word      
        700           5 Song 700 has words       
        800           7 Song 800                 
        900           7 Song 900                 

  9 record(s) selected.

SELECT Book_ID , Title
     , NULLIF(0  , 0 ) AS Song_ID
     , NULLIF('' , '') AS Name
     , 'B  '           AS B_S
 FROM  Books 
 WHERE Title LIKE '%word%'
UNION ALL
SELECT b.Book_ID , b.Title 
     , s.Song_ID
     , s.Name
     , '  S'           AS B_S
 FROM  Songs s
 INNER JOIN
       Books b
   ON  b.Book_ID = s.Book_ID
 WHERE s.Name  LIKE '%word%'
 ORDER BY
       Book_ID
     , B_S     DESC
     , Song_ID
;
------------------------------------------------------------------------------

BOOK_ID     TITLE            SONG_ID     NAME                      B_S
----------- ---------------- ----------- ------------------------- ---
          1 Book A                   100 A word from Song 100        S
          3 Book C                   200 Song 200 word & words       S
          3 Book C                   300 Song 300 has a word in it   S
          3 Book C                   500 Song 500 has some words     S
          4 Book is a word             - -                         B  
          5 words in Book E            - -                         B     <--- Book_ID = 5, without Song
          5 words in Book E          600 Song 600 has a word         S   <--- Book_ID = 5, with Song
          5 words in Book E          700 Song 700 has words          S   <--- Book_ID = 5, with Song
          7 Book G: for word           - -                         B  

  9 record(s) selected.

SELECT b.Book_ID
     , b.Title 
     , s.Song_ID
     , s.Name
     , CASE WHEN b.Title LIKE '%word%' THEN  'B' ELSE  ' ' END
       ||
       CASE WHEN s.Name  LIKE '%word%' THEN ' S' ELSE '  ' END
       AS B_S
 FROM  Books b
 LEFT  OUTER JOIN
       Songs s
   ON  s.Book_ID = b.Book_ID
   AND s.Name    LIKE '%word%'
 WHERE b.Title   LIKE '%word%'
   OR  s.Song_ID IS NOT NULL
 ORDER BY
       Book_ID
     , Song_ID
;
------------------------------------------------------------------------------

BOOK_ID     TITLE            SONG_ID     NAME                      B_S
----------- ---------------- ----------- ------------------------- ---
          1 Book A                   100 A word from Song 100        S
          3 Book C                   200 Song 200 word & words       S
          3 Book C                   300 Song 300 has a word in it   S
          3 Book C                   500 Song 500 has some words     S
          4 Book is a word             - -                         B  
          5 words in Book E          600 Song 600 has a word       B S   <--- Book_ID = 5, with Song
          5 words in Book E          700 Song 700 has words        B S   <--- Book_ID = 5, with Song
          7 Book G: for word           - -                         B  

  8 record(s) selected.

Result:

Conforms to Core SQL-2003

The following vendor reserved word is used:

NAME