SQL query for SQL Server backups
This query will return you all the columns of both tables, you can add your desired column in second query of simple joins without CTE 

SELECT *
FROM msdb.dbo.backupset s
     INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id 
     ORDER BY backup_start_date DESC, backup_finish_date


Query2:  

Select  s.database_name, 
          m.physical_device_name,
          CAST(DATEDIFF(second, s.backup_start_date,s.backup_finish_date) 
            AS VARCHAR(100)) + ' ' + 'Seconds' TimeTaken,s.backup_start_date,
          CASE s.[type]
           WHEN 'D' THEN 'Full'
           WHEN 'I' THEN 'Differential'
           WHEN 'L' THEN 'Transaction Log'
          END AS BackupType,
          s.server_name,
          s.recovery_model
     FROM msdb.dbo.backupset s
     INNER JOIN msdb.dbo.backupmediafamily m ON s.media_set_id = m.media_set_id 
     ORDER BY backup_start_date DESC, backup_finish_date
     GO


Reference Link