Magento 2 How to get all customer shipping address

Question

How do get all customer shipping address?

Magento 2.3.5-p1 version

for the customer table, we can use - customer_entity table name

for the customer address table, we can use - customer_address_entity table name

On customer_entity we can see there is a column of default_billing and default_shipping but that column has a specific address id for this I am unable to find out all customer shipping address only.

Note: Need to just check from the database, not from code.

Answer 1

I believe the customer_address_entity table may be what you are looking for. It has all the address fields such as street, city, country, etc...

Answer 2

This will return the default shipping address for all customers

    SELECT 
        c.entity_id,
        c.email,
        e.*
    FROM 
        customer_entity c 
    INNER JOIN
        customer_address_entity e 
    ON 
        c.default_shipping = e.entity_id;

This will return the addresses that were used at least once on a customer order.

SELECT 
    c.entity_id,
    c.email,
    sa.*
FROM 
    customer_entity c 
INNER JOIN
    sales_order s
ON
    c.entity_id = s.customer_id
INNER JOIN
    sales_order_address sa
ON
    s.entity_id = sa.parent_id AND
    s.shipping_address_id = sa.entity_id;

Let me know if you need any further help !