Ok i have 3 tables, client, order, orderclient. If client made order in orderclient table inserts clientid and orderid. But i can have client without orders and orders without client, so no information about them in orderclient table, so how i can select all of this in one query (mysql)? I mean

clientid orderid
1        1
null     2
2        null

SELECT * FROM test1.clients;

# clientid, name
'1', 'c1'
'2', 'x1'

SELECT * FROM test1.orders;

# orderid, name
'1', 'ds'
'2', 'd'

SELECT * FROM test1.clientorder;

# clientorderid, clientid, orderid
'1', '1', '1'

This does not return clients without order. SELECT o.,c. FROM orders o LEFT JOIN
(clients c LEFT JOIN clientorder oc ON c.clientid = oc.clientid ) ON o.orderid = oc.orderid

# orderid, name, clientid, name
'1', 'ds', '1', 'c1'
'2', 'd', NULL, NULL

Here is schema to test -

DROP TABLE IF EXISTS `clientorder`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `clientorder` (
  `clientorderid` int NOT NULL,
  `clientid` int DEFAULT NULL,
  `orderid` int DEFAULT NULL,
  PRIMARY KEY (`clientorderid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `clientorder`
--

LOCK TABLES `clientorder` WRITE;
/*!40000 ALTER TABLE `clientorder` DISABLE KEYS */;
INSERT INTO `clientorder` VALUES (1,1,1);
/*!40000 ALTER TABLE `clientorder` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `clients`
--

DROP TABLE IF EXISTS `clients`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `clients` (
  `clientid` int NOT NULL,
  `name` varchar(3) DEFAULT NULL,
  PRIMARY KEY (`clientid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `clients`
--

LOCK TABLES `clients` WRITE;
/*!40000 ALTER TABLE `clients` DISABLE KEYS */;
INSERT INTO `clients` VALUES (1,'c1'),(2,'x1');
/*!40000 ALTER TABLE `clients` ENABLE KEYS */;
UNLOCK TABLES;

--
-- Table structure for table `orders`
--

DROP TABLE IF EXISTS `orders`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!50503 SET character_set_client = utf8mb4 */;
CREATE TABLE `orders` (
  `orderid` int NOT NULL,
  `name` varchar(3) DEFAULT NULL,
  PRIMARY KEY (`orderid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
/*!40101 SET character_set_client = @saved_cs_client */;

--
-- Dumping data for table `orders`
--

LOCK TABLES `orders` WRITE;
/*!40000 ALTER TABLE `orders` DISABLE KEYS */;
INSERT INTO `orders` VALUES (1,'ds'),(2,'d');
/*!40000 ALTER TABLE `orders` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2022-09-17  4:20:52

CodePudding user response：

You need to FULL OUTER JOIN but myql doesn't support it, so you need to rebuild it

SELECT c2.*,o.*
FROM
 clients c2 LEFT JOIN (`orders` o LEFT JOIN  
 clientorder oc   ON o.orderid = oc.orderid) ON c2.clientid = oc.clientid
union 
SELECT c2.*,o.*
FROM
  (`orders` o LEFT JOIN  
 clientorder oc   ON o.orderid = oc.orderid) left JOIN clients c2 ON c2.clientid = oc.clientid

fiddle