title: MySQL join with Direct table or subfields of table
body: <p>First query:</p>
  
  <pre><code>SELECT  
      u.id,
      u.first_name,
      u.last_name,
      u.tazkera_id,
      cu.relation_type,
      CASE csp.`shift`
          WHEN '1' THEN 'Morning'
          WHEN '2' THEN 'Afternoon'
          else 'Neither'
      END AS shift
  FROM `course_user` as cu
  LEFT JOIN `courses` as c ON c.id = cu.`course_id`
  LEFT JOIN (
      select users.id,
             users.first_name,
             users.last_name,
             users.tazkera_id
             FROM `users`
  ) as u ON u.id = cu.`user_id`
  
  left JOIN `course_schedule_prefs`   as csp ON csp.`user_id` = cu.`user_id`
  
  Where cu.relation_type = 1 group by cu.`user_id`;
  </code></pre>
  
  <p>Second Query: </p>
  
  <pre><code>SELECT  
      u.id,
      u.first_name,
      u.last_name,
      u.tazkera_id,
      cu.relation_type,
      CASE csp.`shift`
          WHEN '1' THEN 'Morning'
          WHEN '2' THEN 'Afternoon'
          else 'Neither'
      END AS shift
  FROM `course_user` as cu
  LEFT JOIN `courses` as c ON c.id = cu.`course_id`
  LEFT JOIN users as u ON u.id = cu.`user_id`
  left JOIN `course_schedule_prefs`   as csp ON csp.`user_id` = cu.`user_id`
  Where cu.relation_type = 1 group by cu.`user_id`;
  </code></pre>
  
  <p>Tables :</p>
  
  <p>users:</p>
  
  <pre><code>CREATE TABLE `users` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `first_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `last_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `email` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `password` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `is_password_reset` int(11) NOT NULL DEFAULT '0',
    `login_ip` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `login_date` datetime DEFAULT NULL,
    `last_login_ip` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `last_login_date` datetime DEFAULT NULL,
    `is_email_address_verified` int(11) NOT NULL DEFAULT '0',
    `failed_login_attempts` int(11) DEFAULT NULL,
    `last_failed_login_date` datetime DEFAULT NULL,
    `tazkera_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `province_code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `district_code` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `village` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `home_address` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `promote_unique_user_id` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `remember_token` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
    `forgot_token` varchar(100) COLLATE utf8_unicode_ci DEFAULT NULL,
    `email_confirm_token` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `district_other` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `father_name` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `training_provider_id` int(10) unsigned DEFAULT NULL,
    `is_profile_completed` int(11) DEFAULT NULL,
    `active` int(11) DEFAULT '1',
    `picture` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `small_picture` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `last_poll` bigint(20) DEFAULT NULL,
    `last_password_reset` date DEFAULT NULL,
    `presence_status` int(11) DEFAULT NULL,
    `auto_presence` int(11) NOT NULL DEFAULT '0',
    `last_activity` timestamp NULL DEFAULT NULL,
    `trash` tinyint(4) NOT NULL DEFAULT '0',
    `login_approval` tinyint(4) NOT NULL DEFAULT '0',
    `lang` varchar(2) COLLATE utf8_unicode_ci NOT NULL DEFAULT 'en',
    `cover` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `small_cover` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `forum_reputation` int(11) NOT NULL DEFAULT '0',
    `forum_suspension_date` date DEFAULT NULL,
    `forum_status` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `users_email_unique` (`email`),
    KEY `users_training_provider_id_foreign` (`training_provider_id`),
    CONSTRAINT `users_training_provider_id_foreign` FOREIGN KEY (`training_provider_id`) REFERENCES `training_providers` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB AUTO_INCREMENT=1000038 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
  </code></pre>
  
  <p>course_users:</p>
  
  <pre><code>CREATE TABLE `course_user` (
    `course_id` int(10) unsigned DEFAULT NULL,
    `user_id` int(10) unsigned DEFAULT NULL,
    `relation_type` int(11) DEFAULT NULL,
    `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    KEY `course_user_course_id_foreign` (`course_id`),
    KEY `course_user_user_id_foreign` (`user_id`),
    CONSTRAINT `course_user_course_id_foreign` FOREIGN KEY (`course_id`) REFERENCES `courses` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `course_user_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
  </code></pre>
  
  <p>courses;</p>
  
  <pre><code>CREATE TABLE `courses` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `curriculum_id` int(10) unsigned DEFAULT NULL,
    `training_center_id` int(10) unsigned DEFAULT NULL,
    `training_coordinator_id` int(10) unsigned DEFAULT NULL,
    `focal_point_id` int(10) unsigned DEFAULT NULL,
    `start_date` date DEFAULT NULL,
    `end_date` date DEFAULT NULL,
    `start_time` time DEFAULT NULL,
    `end_time` time DEFAULT NULL,
    `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `shift` int(11) DEFAULT NULL,
    `stage_id` int(10) unsigned DEFAULT NULL,
    `stream_id` int(10) unsigned DEFAULT NULL,
    `unit_id` int(10) unsigned DEFAULT NULL,
    `master_trainer_id` int(10) unsigned DEFAULT NULL,
    `status` int(11) DEFAULT NULL,
    `pre_test_generated` int(11) NOT NULL DEFAULT '0',
    `post_test_generated` int(11) NOT NULL DEFAULT '0',
    `conduct_days` int(11) DEFAULT NULL,
    `no` varchar(255) COLLATE utf8_unicode_ci DEFAULT NULL,
    `evaluation_available` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `courses_curriculum_id_foreign` (`curriculum_id`),
    KEY `courses_training_coordinator_id_foreign` (`training_coordinator_id`),
    KEY `courses_focal_point_id_foreign` (`focal_point_id`),
    KEY `courses_stage_id_foreign` (`stage_id`),
    KEY `courses_stream_id_foreign` (`stream_id`),
    KEY `courses_unit_id_foreign` (`unit_id`),
    KEY `courses_master_trainer_id_foreign` (`master_trainer_id`),
    KEY `courses_training_center_id_foreign` (`training_center_id`),
    CONSTRAINT `courses_curriculum_id_foreign` FOREIGN KEY (`curriculum_id`) REFERENCES `curriculums` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `courses_focal_point_id_foreign` FOREIGN KEY (`focal_point_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `courses_master_trainer_id_foreign` FOREIGN KEY (`master_trainer_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `courses_stage_id_foreign` FOREIGN KEY (`stage_id`) REFERENCES `stages` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `courses_stream_id_foreign` FOREIGN KEY (`stream_id`) REFERENCES `streams` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `courses_training_center_id_foreign` FOREIGN KEY (`training_center_id`) REFERENCES `training_centers` (`id`),
    CONSTRAINT `courses_training_coordinator_id_foreign` FOREIGN KEY (`training_coordinator_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE,
    CONSTRAINT `courses_unit_id_foreign` FOREIGN KEY (`unit_id`) REFERENCES `units` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB AUTO_INCREMENT=139 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  </code></pre>
  
  <p>course_schedule_prefs;</p>
  
  <pre><code>CREATE TABLE `course_schedule_prefs` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `cunduct_days` int(11) DEFAULT NULL,
    `training_centers` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `shift` int(11) NOT NULL,
    `created_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `updated_at` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
    `user_id` int(10) unsigned DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `course_schedule_prefs_user_id_foreign` (`user_id`),
    CONSTRAINT `course_schedule_prefs_user_id_foreign` FOREIGN KEY (`user_id`) REFERENCES `users` (`id`) ON DELETE CASCADE ON UPDATE CASCADE
  ) ENGINE=InnoDB AUTO_INCREMENT=24 DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci
  </code></pre>
  
  <p>My question is , which one of this query is good in term of performance and accuracy. Why ? </p>
  
  <p>first query: I join directly with table; select all columns of table.</p>
  
  <p>Second query: I used subquery inside of join; select only those element which I need it.</p>
  
  <p>Note : My main question is about <code>users</code> table which is in join</p>